Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

powerbihelp1245_0-1600152498122.png

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

6 REPLIES 6
tex628
Community Champion
Community Champion

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

/ J


Connect on LinkedIn
Anonymous
Not applicable

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

Anonymous
Not applicable

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
Community Champion
Community Champion

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


Connect on LinkedIn
Anonymous
Not applicable

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

 

Hi @Anonymous ,

This looks like a measure totals problem. You can refer this similar issue:

Dealing with Measure Totals 

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:

Measure Totals, The Final Word 

 

In your case, you have quoted calculate() function twice in your modified measure, it is not meaningful, and you can follow the above gallery and issue to try to re-modify your measure since there is no specific data in this issue.

 

In addition, I have attached a sample file about measure totals in the below that you can refer, although the data is not your required, the logic is simliar, hopes to help you.

 

Best Regards,
Yingjie Li

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.