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

thedesk

New Member
Joined
Feb 16, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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),"")
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
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

New Member
Joined
Feb 16, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
what did you learn when you placed the measures that are embedded inside your IF statement into the pivot table?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,290
Members
418,186
Latest member
mrahim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top