Forum Discussion
Measures with Hierarcical data
I got it!
Here is the measure I used to make it work:
SUMX(KEEPFILTERS(VALUES('Filter - MMs Only in Both Periods 1 & 2')),(CALCULATE(AVERAGE('ZPUR_O01 - Periods 1 & 2'[Unit Price (USD)])*[FILTER - PO Qty Period 1 total for Year], KEEPFILTERS(VALUES('ZPUR_O01 - Periods 1 & 2'[Material])))))
Greg,
The source data has about 40 columns, but here is an example from the relevant fields:
The formulas for the current measures I am using are:
1: Sum of Average Unit Price (USD) per Material =
SUMX(
KEEPFILTERS(VALUES('ZPUR_O01 - Periods 1 & 2'[Material])),
CALCULATE(AVERAGE('ZPUR_O01 - Periods 1 & 2'[Unit Price (USD)]))
)
2: FILTER - PO Qty Period 1 total for Year =
CALCULATE(
[FILTER - PO Qty Period 1],
ALLSELECTED('ZPUR_O01 - Periods 1 & 2'[Start of Quarter].[Year])
)
3: Sum of Average Unit Price (USD) per Material x FILTER - PO Qty Period 1 total for Year =
[Sum of Average Unit Price (USD) per Material] * [FILTER - PO Qty Period 1 total for Year]
4: Sum of Average Unit Price (USD) per Material YoY% =
IF(
ISFILTERED('ZPUR_O01 - Periods 1 & 2'[Start of Quarter]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or
primary date column."),
VAR __PREV_YEAR =
CALCULATE(
[Sum of Average Unit Price (USD) per Material],
DATEADD('ZPUR_O01 - Periods 1 & 2'[Start of Quarter].[Date], -1, YEAR)
)
RETURN
DIVIDE(
[Sum of Average Unit Price (USD) per Material] - __PREV_YEAR,
__PREV_YEAR
)
)
Thank You.
Also,
Here is a screen shot of the Matrix Visual:
The Material Group and Category have accompanying Text fields that I am using for the output.
I got it!
Here is the measure I used to make it work:
SUMX(KEEPFILTERS(VALUES('Filter - MMs Only in Both Periods 1 & 2')),(CALCULATE(AVERAGE('ZPUR_O01 - Periods 1 & 2'[Unit Price (USD)])*[FILTER - PO Qty Period 1 total for Year], KEEPFILTERS(VALUES('ZPUR_O01 - Periods 1 & 2'[Material])))))