Measures with Hierarcical data

[Jwhitehead]

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!!

[Greg_Deckler]

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

[Jwhitehead]

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.

[Jwhitehead]

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.

[Jwhitehead · Accepted solution]

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])))))