Forum Discussion
Measures with Hierarcical data
I am building a report that compares material purchased in the previous quarter with material purchased in the previous quarter - 1 year prior. The database captures each transaction for all material in those periods.
How the Matrix visualization shows:
| Period 2 (Q2 - 2018) | Period 1 (Q1 - 2018) | ||||||
| Average Unit Price | Quantity | Value | Average Unit Price | Quantity | Value | YoY % Change | |
| Group 1 | $726.24 | 7 | $5,083.66 | $897.87 | 7 | $6,285.09 | 23.63% |
| Category 1 | $726.24 | 7 | $5,083.66 | $897.87 | 7 | $6,285.09 | 23.63% |
| Material 1 | $503.83 | 1 | $503.83 | $574.77 | 1 | $574.77 | 14.08% |
| Material 2 | $170.85 | 4 | $683.40 | $245.75 | 4 | $983.00 | 43.84% |
| Material 3 | $51.56 | 2 | $103.12 | $77.35 | 2 | $154.70 | 50.02% |
How I need it to show:
| Period 2 (Q2 - 2018) | Period 1 (Q1 - 2018) | ||||||
| Average Unit Price | Quantity | Value | Average Unit Price | Quantity | Value | YoY % Change | |
| Group 1 | $976.58 | 8 | $1,540.69 | $1,143.74 | 8 | $1,958.34 | 17.12% |
| Category 1 | $726.24 | 7 | $1,290.35 | $897.87 | 7 | $1,712.47 | 23.63% |
| Material 1 | $503.83 | 1 | $503.83 | $574.77 | 1 | $574.77 | 14.08% |
| Material 2 | $170.85 | 4 | $683.40 | $245.75 | 4 | $983.00 | 43.84% |
| Material 3 | $51.56 | 2 | $103.12 | $77.35 | 2 | $154.70 | 50.02% |
| Category 2 | $250.34 | 1 | $250.34 | $245.87 | 1 | $245.87 | 23.63% |
| Material 4 | $250.34 | 1 | $250.34 | $245.87 | 1 | $245.87 | 98.21% |
The current version shows the 'Value' of Group 1 as $5083.66 (a result of the measure Avg Unit Price * Quantity). The result should be $1540.69 (a summation of each Category in the Group).
The measures I have attempted so far can give the correct values at the category and/or group level, but will in turn show THOSE results repetatively at the material level instead of the correct results for each material.
How do I get the measure (Avg Unit Price * QTY) to work at the material level, but have each category sum the results of the material in the category and the Group sum the results of each category in the group?
Thank you for the help!!
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])))))
4 Replies
- Greg_Deckler
Community Champion
Going to need sample/example source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 orprimary 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.
