# Two different measures on the same Powerpivot report column

#### kdankwah

##### New Member
I need an advice on how to do this. I have a Power-pivot summary report that I create every month. 90% of the report row uses this measure for the column MTD:

=CALCULATE (SUM([AMOUNT]),

FILTER (
ALL ( DATE ),
'Date'[YearMonthNumber]= MAX ( 'Date'[YearMonthNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)

and the YTD measure is:

=CALCULATE (SUM([AMOUNT]),
FILTER (
ALL ( DATE ),
'Date'[YearNumber] = MAX ( 'Date'[YearNumber] )
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)

This is working perfectly.

The problem is one of the rows in the report will have a different measure, I am using an IF statement below to extract the mtd and ytd figures if the code is OADPO.

I don't know how that will work, its not working anyway. Is this possible in a report?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### kdankwah

##### New Member
Disregard the last statement, Correction I want to formulate the last If statement measure to the MTD and the YTD to create the two columns.

Thanks

Last edited:

#### scottsen

##### Well-known Member
You will want something like this:

Code:
``````=IF (HASONEVALUE('Combined Years Dataset'[SOURCE]),
IF (VALUES('Combined Years Dataset'[SOURCE] = "OADPO"),
[MTD],
[YTD]
)
)``````

This IF check is to avoid passing multiple values to VALUES() in the grand total cell (which causes blow up). Can also be used to do something different in those total cells

#### kdankwah

##### New Member
You will want something like this:

Code:
``````=IF (HASONEVALUE('Combined Years Dataset'[SOURCE]),
IF (VALUES('Combined Years Dataset'[SOURCE] = "OADPO"),
[MTD],
[YTD]
)
)``````

This IF check is to avoid passing multiple values to VALUES() in the grand total cell (which causes blow up). Can also be used to do something different in those total cells

Thank you so much. I appreciate your help. I have thought of doing it sthis way too. I will create two different pivot table reports one for regular and the other for the Purchase Order report. I will then use OLAP cubes to combine and rearrange them. Is my approach OK.

Thanks once again.

#### scottsen

##### Well-known Member
Sorry, I don't totally understand your scenario, so it's hard for me to say. I don't know what is in "regular" report and PO report, or what you hope to "combine" about them...

Replies
1
Views
406
Replies
0
Views
189
Replies
2
Views
555
Replies
2
Views
331
Replies
0
Views
242

1,195,687
Messages
6,011,152
Members
441,589
Latest member
tommatwalker

### 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.

### Which adblocker are you using?

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

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