Measure total wrong when aggregated from month up to the Quarter

[Anonymous]

Using this measure works fine at the month level.   But if I roll it up to the Quarter level the totals are not correct.   The "Factor Table" is connected to my date table.  

You can see the rows below in the "50 Factor" column (my measure) definitely do not add up to the total that is shown.   That is what I am trying to fix.  

50 Factor =

[50% Prob Total]

* SUMX (

FILTER ( 'Factor Table', 'Factor Table'[Metric] = "50%" ),

'Factor Table'[Factor %]

)

[v-shex-msft]

HI @Anonymous,

It seems like a common measure total issue when expression calculates with multiple aggregations. I'd like to suggest you take a look at Greg's blog if it helps with your scenario:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[AlexisOlson]

Your measure is essentially computing [50% Prob Total] * SUM ( 'Factor Table'[Factor %] ).

I suspect you want something more like this where [50% Prob Total] is inside the SUMX:

50 Factor =
SUMX (
    VALUES ( 'Factor Table'[Factor %] ),
    [50% Prob Total] * 'Factor Table'[Factor %]
)

[Anonymous · Accepted solution]

@AlexisOlson    Hi Alexis   -  Unfortunately that still gives me the same results.   

I did end up with a solution that worked by creating another measure  (which references my original measure).   But yes, ideally I would like to have just one measure, not two.    Here is the new measure which works perfectly at the month level and quarter.     

50 Factor Fix =

SUMX (

SUMMARIZE (

'Sf_Opportunity Line',

'Sf_Opportunity Line'[Opportunity Name_Line],

"50 Factor_", [50 Factor]

),

[50 Factor_]

)

[AlexisOlson]

It can be done in one measure but you need to iterate over the appropriate level of granularity (which I couldn't determine from your image).

I think this might work but it's hard to say for sure without having something to test against:

50 Factor Fix =
SUMX (
    VALUES ( 'Sf_Opportunity Line'[Opportunity Name_Line] ),
    [50% Prob Total]
        * CALCULATE ( SUM ( 'Factor Table'[Factor %] ), 'Factor Table'[Metric] = "50%" )
)

[Anonymous]

@AlexisOlson    Thanks Alexis.   That did not end up working (it still aggregated incorrectly when rolled up to the quarter level).    Just staying with my original "fix" for now.