Forum Discussion

dapperscavenger's avatar
dapperscavenger
Helper V
5 years ago
Solved

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:

 

 

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!

Need Help
  • dapperscavenger's avatar
    dapperscavenger
    5 years ago

    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:

     

6 Replies

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      5 years ago

      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's avatar
        dapperscavenger
        Helper V
        5 years ago

        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!