DAX Measure works, but totals don't when using an IF Statement

[thedesk]

Hi there,

i am new to PowerPivot and DAX, but am managing to set up DAX Measures and they work, apart from where it is filtered out with IF statements, whereby it is returning the correct value in the measure but the totals do not match this.

My DAX for this column is.... All of the [] are existing measures.


=if([Total Samples]>0,

if([KPI Target]>[TAT Achieved %],

[Sum of Total Cost]*.03),"")

 

[Matt Allington]

To understand what is going on, you should add the measures that are part of the IF statement directly into the pivot table and look at the value they return at the total level. When in the pivot, they are filtered by the rows of the pivot. When they are at the total level, there is no filtering, hence the aggregates may not do what you want. the solution depends on what you want.

 

[thedesk]

There IF statement is based on the total row grouping and if they have reached the target or not. It can not be based on each line within the table as at this stage we can’t tell if they have reached the target.

although the measure works perfectly on each line in the pivot table, the totals is applying the IF statement to the totals aswell instead of returning the sum based on the column.

 

[Matt Allington]

what did you learn when you placed the measures that are embedded inside your IF statement into the pivot table?