Need urgent help, Incorrect measure totals

[Anonymous]

Hi community,

I have been struggling with this for a while, hope someone can help me out..

Following is the snapshot of my measure totals that are incorrect(higlighted in yellow):

I tried a workaround using hasonefilter & Allexcept on one of the incorrect measures, this time it gives cumulative totals and no row subtotal as following:

I want just the totals(not cumulative) and a correct subtotal.

My PBI is attached:

https://1drv.ms/u/s!Ag919_pO_UKrdZt7uM4Bkx2JrAY?e=7LPVBD

Please help,

Many thanks in advance.

[v-xicai]

Hi @Anonymous ,

You can add SUMMARIZE and SUMX function in your formula, take the measure [Difference] as example, change it like DAX below, do the same with [Fortnightly Payment] and other measures, just change the red parts of formula.

Difference =
var d= [Total_Billing]- [Tot fortnightly payment]    --[Difference] original formula
var _table = SUMMARIZE('Dim week ending_new', 'Dim week ending_new'[Empcost_period end dt],"_Value", d)
return
IF(HASONEVALUE('Dim week ending_new'[Empcost_period end dt]), d, SUMX(_table,[_Value]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-xicai for your solution. I used the suggested DAX to calculate Grand Difference:

Difference =
var d= [Total_Billing]- [Tot fortnightly payment]    --[Difference] original formula
var _table = SUMMARIZE('Dim week ending_new', 'Dim week ending_new'[Empcost_period end dt],"_Value", d)
return
IF(HASONEVALUE('Dim week ending_new'[Empcost_period end dt]), d, SUMX(_table,[_Value]))

But the Measure total is still incorrect as following:

Any more suggestions?

Thanks,

Meena