Help with Measure Totals

[dapperscavenger]

I have the dreaded measure totals issue, but I can't figure out how to fix it.

Here is my table:

This is the measure for UoM Include :

UoM Include = 
CALCULATE(

SWITCH(TRUE(), 
VALUES(SwitchUoM[SwitchUoM]) = "Value GBP", [Sum Value by Market], 
VALUES(SwitchUoM[SwitchUoM]) = "Volume AC", [Sum Volume AC by Market],
VALUES(SwitchUoM[SwitchUoM]) = "Volume EU", [Sum Volume EU by Market], 
VALUES(SwitchUoM[SwitchUoM]) = "No. of SKUs", [Sum Distinct SKUs by Market], 
[Sum Volume 9L by Market])

, FILTER(ALL(Inventory[InventoryDate]) , Inventory[InventoryDate] = MAX(Dates[Date])),
ALL(Inventory[Action])
)+0

This is an example of the measures that the above switch refers to:

Sum Volume AC by Market = 
sumx(summarize( MarketSplit, MarketSplit[SKU],MarketSplit[Sales Office],"_1", maxx(filter(Inventory, Inventory[SKU] = max(MarketSplit[SKU]) ),Sum(Inventory[Quantity AC])) ,"_2",max(MarketSplit[PercentSplit])), [_1]*[_2])

How can I make the totals add up correctly in my table?

Thank you!

[dapperscavenger]

I have dramatically edited the above post as I know more about the issue now.  

I have been reading this post, which seems to help, but I can't quite figure it out:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Any help appreciated!

[v-kkf-msft]

Hi @dapperscavenger ,

Try the following formula:

m_Total 1 = 
VAR __table = 
    SUMMARIZE(
        'SwitchUoM',
        SwitchUoM[SwitchUoM],
        "__value",[UoM Include]
    )
RETURN
    IF(
        HASONEVALUE(SwitchUoM[SwitchUoM]),
        [UoM Include],
        SUMX(__table,[__value])
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[dapperscavenger]

Thanks Winniz

This formula gives the same result:

I would like to see the total line show the sum, which in example above is approx 264

Not sure where 584.6K is coming from!

[v-kkf-msft]

Hi @dapperscavenger ,

Did I answer your question? If you can get the correct result, please accept as solution so that users with the same problem can find it quickly. If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @dapperscavenger ,

Try the following formula:

Uom Include_Sumx = 
IF(
    HASONEVALUE(MarketSplit[SKU]),
    [UoM Include],
    SUMX(MarketSplit,[UoM Include])
)

 If it is incorrect, can you provide your PBIX file? Or provide some sample data that don't contain sensitive data and relationships between tables. 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[dapperscavenger · Accepted solution]

Hi Winniz

Thank you for your support

I was anonymising the data for upload (which was taking a long time as it is a huge dataset) when I sort of fell upon the solution.

This filter was the culprit. 

ALL(Inventory[Action])

I took the Actions and put them in a sperate table with an inactive relationship.

Then I changed the formula to this (Using USERELATIONSHIP):

UoM Include Market = 
CALCULATE(

SWITCH(TRUE(), 
VALUES(SwitchUoM[SwitchUoM]) = "Value GBP", [Sum Value by Market], 
VALUES(SwitchUoM[SwitchUoM]) = "Volume AC", [Sum Volume AC by Market],
VALUES(SwitchUoM[SwitchUoM]) = "Volume EU", [Sum Volume EU by Market], 
VALUES(SwitchUoM[SwitchUoM]) = "No. of SKUs", [Sum Distinct SKUs by Market], 
[Sum Volume 9L by Market]), 

USERELATIONSHIP(Actions[Actions], Inventory[Action]),
FILTER(ALL(Inventory[InventoryDate]) , Inventory[InventoryDate] = MAX(Dates[Date]))
)+0

And now you can see the totals are adding correctly: