Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YC
Frequent Visitor

Measure for calculating average of past 10 quarters averages

Hello! I am new to DAX PBI and I hope somebody can help me with my problem. 

1. My initial data is stated in below "table1": price per material, for each quarter:

MaterialQ1 Year1 priceQ2 Year1 price

Q3

Year1 price

Q4 Year1 priceQ1 Year2 priceQ2 Year2 price

Q3

Year2 price

Q4 Year2 priceQ1 Year3 priceQ2 Year3 price

A

11  0.50.50.511.51.5
B2.52.52.52.52.52.52.52.52.52.5
C32.5  111111
D23  322222

 

2. I need to calculate the price trends from Q1 Y1 to Q2 Y3 so that I know how many materials had price increase, price decrease or remained flat. I was thinking to do it like this:

 - A) create a measure for each comparison of quarter vs previous quarter to get the % change, as per below table:

MaterialQ1 Year1 %Q2 vs Q1 Year1 %

Q3 vs Q2

Year1 %

Q4vs Q3 Year1 %Q1 Year2 vs Q4Year1 %Q2 vs Q1 Year2 %

Q3 vs Q2

Year2 %

Q4 vs Q3 Year2 %Q1 Year3vs Q4 Year2 %Q2 vs Q1 Year3 %

A

 

0%

   

0%

0%100%50%0%
B 0%0%0%0%0%0%0%0%0%
C -17%   0%0%0%0%0%
D 50%   -33%0%0%0%0%

 

- B) then to create a new measure that will calculate the average of all 10 averages above per each material. And here my problem appeared, cause I have tried to do this measure like this (below it's just an example for 2 measures, but I want to add all above comparison measures from step A):

calculate(averagex ('table1', union(row(''value",measure[Q2 vs Q1 Year1 %]),row("value",measure[ Q3 vs Q2Year1 %]),groupby('table1",[Material]))

I should get to something like this:

MaterialTotal average (Q1 Y1 - Q2 Y3)

A

25%
B0%
C-3%
D3%

The problem is that this measure is not working...I don't get any error, but still it doesn't return any value.

 Can somebody help me understand what am I doing wrong? Or maybe there is another method to obtain the result I want?

 

Thank you!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @YC 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

All steps are numbered in front of each measure.

I suggest having data model like below.

 

Picture1.png

 

https://www.dropbox.com/s/owhwzxwxm5gfjtz/YC.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, @YC 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

All steps are numbered in front of each measure.

I suggest having data model like below.

 

Picture1.png

 

https://www.dropbox.com/s/owhwzxwxm5gfjtz/YC.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


YC
Frequent Visitor

Thank you! I managed to solve my problem with your help!

amitchandak
Super User
Super User

@YC , if the first table is your data format, I would advise to unpivot that

https://radacad.com/pivot-and-unpivot-with-power-bi

 

Create a qtr year table with YYYYQQ as one of the columns (say date)

 

then you can rank on yyyy qq

 

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

 

You can create measures like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

Last 10 Qtr = CALCULATE(Average('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-10 && 'Date'[Qtr Rank]=<max('Date'[Qtr Rank]) ))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.