Forum Discussion
Measure with condition IF do not summarize
Hello Anonymous
The problem you are seeing with the total row is that it has no concept of the rows in the matrix. It is calculated as if you took the PN field out of your visual. What you want to do is add a SUMX to your calculation that will force it to iterate over the PN list.
Something along these lines.
No Future Usage = SUMX ( VALUES ( YourTable[PN] ); IF ( [Requirements] + [ReqsBulk] = 0; [Valid Stock Value]; 0 ) )This will do the base calculation over all the individual PN then sum the result of each of those.
Hello Anonymous
The problem you are seeing with the total row is that it has no concept of the rows in the matrix. It is calculated as if you took the PN field out of your visual. What you want to do is add a SUMX to your calculation that will force it to iterate over the PN list.
Something along these lines.
No Future Usage =
SUMX (
VALUES ( YourTable[PN] );
IF ( [Requirements] + [ReqsBulk] = 0; [Valid Stock Value]; 0 )
)
This will do the base calculation over all the individual PN then sum the result of each of those.
Awesome! It worked as a charm!
Before this, I tried doing it with a CALCULATE but it didn't work:
No future usage = CALCULATE([Valid Stock Value];fRequirements[Quantity]=0)
Can you spot what is wrong here?
The SUMX turns the measure into an iterator that runs the calculation against the entire table we fed it
VALUES ( YourTable[PN] )
Then once it has the calculations for that table it sums the amount. On an individual row the calculation is for a single PN but on the total row it is the sum of that calc on the table we fed in. If you don't do it as an interator it runs the total against no PN.