Measure Total not correct

[Anonymous]

I'm am calculating "Price" x "Order" with the measure "To Order".  When placed in the table, the total is correct for each individual line, whoever, if I place this measure in a tile by itself, the total is $993,077.18.  If I order the three lines, I will only spend around $31,000.  How can I adjust the measure to show only the total for the three lines in the table?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may try a similar way as shown here.

[Anonymous]

Thank you both for your help on this.  I'm sure both solutions would work but I was able to fix my issue with the Summarize function you referenced.  

[Anonymous]

This is just silly having to do this? How long have we had pivot tables and summary values......why the hell does it not just adjust the subtotal as you filter, by default....Crazy. Utter madness and causing me a great deal of wasted time on something which is obvious.

[Anonymous]

Hey @Anonymous

You can get around this by specifying how you want the Total row calculated using ISFILTERED and SUMX. I created a short video tutorial here.

https://www.youtube.com/watch?v=4NgQLqHI6hU

Hope this helps,

Parker

[Anonymous]

Thanks for the help.  I tried a couple different ways and I can't get it to work with either.  I can't figure out what I'm doing wrong.

[Anonymous]

@Anonymous

Close, you might want to switch out MFG_CODE for PN. I don't know your data but PN will definitely be filtered by your table. Then the only other change you need to make is that the SUMX should be the last parameter of the IF statement. Something like:

IF(
   ISFILTERED(xxx),
   [Measure1] * [Measure2],
   SUMX(xxx)
)

-Parker

[Anonymous]

That is not working either.  I feel it might be I am trying to sumx on the "Measure Table", which is just a table i created to hold all of my measures.  Would that be causing the problem?

[Anonymous]

@Anonymous

Oh yes, definitely. The first parameter to the SUMX function is the table that you want to iterate through, and the second parameter is the calculation you want to apply to each row of that table. So you probably want to iterate through the PARTS_MASTER table. This way you will be performing a calculation on each row of the PARTS_MASTER table and then summing the results for your total row.

-Parker