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 tag...
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")
    )
)
)

     

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")
    )
)
)

 

 

  

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
5 years ago

Sorry for the delayed response, I've been home sick. 

The [Final tagging] measure only returns the correct value when it's evaluated over a single product. You need to add in as a column in the calculation and then filter on it. Something along the lines of this: 

sc1 = 
SUMX(
FILTER(
SUMMARIZE(inventory,[Product], [As on today stock value in Cr's (mvg price)], "Final Tagging" , [final tagging]), 
[Final Tagging] = "stn low and variation high"),
[As on today stock value in Cr's (mvg price)])


Try this and let me know what you get! 

Br,
J

  • Anonymous's avatar
    Anonymous
    Not applicable
    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")
    )
)
)