Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Table total giving wrong results even after using sumx

Hi, 


To explain my data first, I have list of materials in desc_1 which have an inventory value assoiciated with them from various warehouses in the base data. For each material I have a final tagging measure which uses data from different tables. (refer below image)

I want to sum up the inventory value by filtering on the final tagging. For the same, I use measures sc1 to sc4 for the 4 final tagging. I plan to show the value as % of sc1 of sum of all sc measures in cards.

 

 

 

sc1 = 
var final = [final tagging]

return
SUMX(FILTER(inventory,final = "stn low and variation high"),inventory[As on today stock value in Cr's (mvg price)])

 

 

 

Other sc measure are defined similarly. Note that I do use sumx here. Then I calculate the value% like this

 

 

 

value%1 = IF([sc1]=0,0,[sc1]/[stot])

stot = [sc1] + [sc2] +[sc3] +[sc4]

 

 

 

Now, as you can see in the table, the total of sc1 which is shown is actually the value of sc1 based on the total row, i.e. it is just checking the tagging of the total row and not doing a sumx of sc1 value on each row. For the same reason I don't see a total value in sc4 colum. This is of course then messing with my value% measure.

How can I get the total correctly and hence the correct value%?

 

PS - Unfortunately I can't share my base data due to its sensitivity.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    thanks for your reply but I fiured it out. I had to declare the variable inside sumx as it was computing the variable only once adn not in each iteration.

     

    sum1 = 

SUMX(SUMMARIZE(inventory,inventory[Desc_1]), var final = [final tagging]
return
CALCULATE(
    CALCULATE(
        SUM(inventory[As on today stock value in Cr's (mvg price)]),
        FILTER(SUMMARIZE(inventory,inventory[Desc_1]),
        final = "stn low and variation high")
    )
)
)

     

6 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    The problem has to do with how you use the [Final tagging] measure in the filter statement, whats the code for it? 

    / J

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      removed my wall of code as it was not relevant to the question asked

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        For anyone trying to attempt to solve this issue, I found an intresting article regarding this which I thought might help -  https://exceleratorbi.com.au/double-calculate-solves-sumx-problem/ 

        tex628 

        Basis this, I tried the following code but unfortunately it still didn't work and gave exactly the same result as my measure- 

         

         

        sum1 = 
var final = [final tagging]
return
SUMX(SUMMARIZE(inventory,inventory[Desc_1]),
CALCULATE(
    CALCULATE(
        SUM(inventory[As on today stock value in Cr's (mvg price)]),
        FILTER(SUMMARIZE(inventory,inventory[Desc_1]),
        final = "stn low and variation high")
    )
)
)