Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Is it possible to create a measure, that is an offset of a previous measure?
I am trying to just move the same result one row down in my matrice. And I want it to be dynamic, so when slicers is applied it will still work. See picture below.
Any suggestions @amitchandak ...?
Thanks in advance 🙂
Solved! Go to Solution.
@Sveina , Try like, Magin % by Net , trail by 1 . For more details check file attached and video link below
Margin Behind by net = if([Net Rank Brand]<>1, CALCULATE([Margin %], WINDOW([Net Rank Brand]-1,ABS,[Net Rank Brand]-1,ABS,SUMMARIZE(ALLSELECTED('Item'[Brand]), 'Item'[Brand], "_net",[Net]),ORDERBY([_net],DESC))) , BLANK())
You will not get GT in this case
Rank is
Net Rank Brand = rankx(ALLSELECTED('Item'[Brand]),[Net],,DESC,Dense)
I modified the approch used in this video
Power BI Window function- Pareto Analysis, 80% of sales, Order by on Measure: https://www.youtube.com/watch?v=UVxiLc_AzSs
@Sveina , Try like, Magin % by Net , trail by 1 . For more details check file attached and video link below
Margin Behind by net = if([Net Rank Brand]<>1, CALCULATE([Margin %], WINDOW([Net Rank Brand]-1,ABS,[Net Rank Brand]-1,ABS,SUMMARIZE(ALLSELECTED('Item'[Brand]), 'Item'[Brand], "_net",[Net]),ORDERBY([_net],DESC))) , BLANK())
You will not get GT in this case
Rank is
Net Rank Brand = rankx(ALLSELECTED('Item'[Brand]),[Net],,DESC,Dense)
I modified the approch used in this video
Power BI Window function- Pareto Analysis, 80% of sales, Order by on Measure: https://www.youtube.com/watch?v=UVxiLc_AzSs
Hi @amitchandak
Thanks a lot for your help. Appreciate it a lot.
This actually works fine after I downloaded a newer version of PowerBI. I had a litte problem since your measure of Margin % was different from mine. I used a cumulatice % and that didnt work.
But what I did was to use your formula to offset the income variable, or "Net" in your example, one row down. So i just changed your forumula a bit, like this:
Offset Indeks = if([Net Rank Brand]<>1, CALCULATE([Cumulative Income], WINDOW([Net Rank Brand]-1,ABS,[Net Rank Brand]-1,ABS,SUMMARIZE(ALLSELECTED('Item'[Brand]), 'Item'[Brand], "_net",[Net]),ORDERBY([_net],ASC))) , BLANK())
I called this measure "Offset Index", and it corresponds directly to "Margin behind by net" in your table. I just changed "Margin %" to "Cumulative income".
And then i computed a new variable "Tidligere CDF", where I use the offset function like this
Tidligere CDF = ([Offset index]/CALCULATE(SUM(DATA[Net]),ALLSELECTED()))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |