Post reply

Measure duplicating its value when non-conformed dimension attribute is included

  • June 4, 2025 at 9:41 am

    #4602409

    Hi,

     

    I have two fact tables (say FactA, and FactB), that are joined/shared by three dimensions (dimA/B/C) in-between the two fact tables (conformed dimensions). Fact A contains a list of finance agreements. FactB contains budgeted sales figures, so we have a figure for each month, for each product, for each sales channel. There is a measure - say, SalesCommission - that sums a column for sales amounts in Fact A. We have a variety of measures built on top of FactB.

    The report that displays the sales amounts works fine. However, we wanted to breakdown the sales reporting data of the finance agreements by the type of financial agreement (say we have two types....AgTypeA and AgTypeB). So we have a non-conformed dimension called dimAgreement which contains attributes like the agreement number, and the type of agreement (AgreementType). In FactA there is a relationship between the AgreementID in Fact A and the AgreementID in dimAgreement.

    When we alter the existing report to add in the AgreementType attribute from the dimAgreement dimension so that we can breakdown the sales figures into the two types of agreements, I see the same sales figure for each permutation of agreement types.

    I can tell that when removing the FactB measures from the report, the outputs reverts to expected output. When FactB measures are put back on i can see that rows get duped for each permutation of whichever FactA related dimension attribute is dragged into the report DAX code, and whilst FactA measures report correct number still, FactB measures report their correct figures but on every row. I'm guessing this will be because of the FactA dimensions not being shared with Fact (there would be no reason to do so anyway, because we wouldn't want to have salesbudgets down the same level/grain of any attribute of any FactA related dimension).

    I need a workaround. I have tried adding REMOVEFILTERS('dimAgreement') in (all of) the measures that are built on FactB. And there is no effect - still get the Sales measure reporting the same figure across the different types.

     

    I'm getting this kind of output, very basically....

    • when not dragging in the agreements types...

      • Reported/outputted Sales of all agreements is £10k (and i know that within that, there's £3k agreements in Type A, and £7k of agreements in Type B)

    • when i add in the agreement dimension type attribute...

      • Reported/outputted Sales of all Type A agreement is £10k
      • Reported/outputted Sales of all Type B agreement is £10k

    • I'd expect....

      • Reported/outputted Sales of all Type A agreement is £3k
      • Reported/outputted Sales of all Type B agreement is £7k

    I know the underlying data in the data mart sql database is correct because my t-sql equivalent of the DAX query in the powerbi report produces the expected results, broken down by types of agreements as expected, with the expected sales figures and counts of agreements within each type correctly outputted.

    All help greatly appreciated!

    • This topic was modified 3 months, 2 weeks ago by stiej1977. Reason: clarification and further detail
    • This topic was modified 3 months, 2 weeks ago by stiej1977.
    • This topic was modified 3 months, 2 weeks ago by stiej1977.

  • June 5, 2025 at 10:10 am

    #4602973

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply