cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Helper IV
Helper IV

Help with Measure Totals

 

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

 

Here is my table:

 

Capture1.PNG

 

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!

1 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:

 

Capture.PNG

View solution in original post

6 REPLIES 6
dapperscavenger
Helper IV
Helper IV

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!

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.

Thanks Winniz

 

This formula gives the same result:

 

Capture1.PNG

 

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!

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

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.

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:

 

Capture.PNG

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors