Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Material | Q1 Year1 price | Q2 Year1 price | Q3 Year1 price | Q4 Year1 price | Q1 Year2 price | Q2 Year2 price | Q3 Year2 price | Q4 Year2 price | Q1 Year3 price | Q2 Year3 price |
A | 1 | 1 | 0.5 | 0.5 | 0.5 | 1 | 1.5 | 1.5 | ||
B | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 | 2.5 |
C | 3 | 2.5 | 1 | 1 | 1 | 1 | 1 | 1 | ||
D | 2 | 3 | 3 | 2 | 2 | 2 | 2 | 2 |
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:
Material | Q1 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:
Material | Total average (Q1 Y1 - Q2 Y3) |
A | 25% |
B | 0% |
C | -3% |
D | 3% |
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!
Solved! Go to Solution.
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.
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.
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.
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.
Thank you! I managed to solve my problem with your help!
@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]) ))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |