cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jwhitehead Frequent Visitor
Frequent Visitor

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 PriceQuantityValueAverage Unit PriceQuantityValueYoY % 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.831$503.83$574.771$574.7714.08%
     Material 2$170.854$683.40$245.754$983.0043.84%
     Material 3$51.562$103.12$77.352$154.7050.02%

 

How I need it to show:

 

 Period 2 (Q2 - 2018)  Period 1 (Q1 - 2018)   
 Average Unit PriceQuantityValueAverage Unit PriceQuantityValueYoY % 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.831$503.83$574.771$574.7714.08%
     Material 2$170.854$683.40$245.754$983.0043.84%
     Material 3$51.562$103.12$77.352$154.7050.02%
Category 2$250.34 1$250.34 $245.87 1$245.87 23.63%
     Material 4$250.341$250.34$245.871$245.8798.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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Jwhitehead Frequent Visitor
Frequent Visitor

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

4 REPLIES 4
Super User
Super User

Re: Measures with Hierarcical data

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jwhitehead Frequent Visitor
Frequent Visitor

Re: Measures with Hierarcical data

Greg,

 

The source data has about 40 columns, but here is an example from the relevant fields:

 

  Source Data Example.JPG

 

 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 Frequent Visitor
Frequent Visitor

Re: Measures with Hierarcical data

Also,

 

Here is a screen shot of the Matrix Visual:

 

Matrix Screen Capture.JPG

 

The Material Group and Category have accompanying Text fields that I am using for the output.

Jwhitehead Frequent Visitor
Frequent Visitor

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