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 all,
Currently i am working on a dashboard and i want to make a dynamic table in which i can show a form funnel(based on choice in slicer and how it performs. I can't figure out how to do the two red calculated metrics below.
Slicer | Dynamic Table | ||||
Form A | Steps | Count | step / previous step | step / step 1 | |
Form B | A | 70 | 100% | 100% | |
Form C | B | 48 | 69% | 69% | |
C | 46 | 96% | 66% | ||
D | 34 | 74% | 49% | ||
E | 26 | 76% | 37% |
- Step / Previous step
I thougt something like DIVIDE( SUM(Table[Count]) , CALCULATE(SUM(Table[Count]),FILTER(ALL(Table),Table[Steps] = MAX(Table[Steps])-1)) ) was working for this calculation but to bad... It didn't work this time.
- Step / Step 1
Here i thought this would work: DIVIDE( SUM(Table[Count]) , CALCULATE(SUM(Table[Count]), Table[Steps] = "A") ) but that wasn't working either...
I'm out of options that i know, so i would really appreciate if someone could help me with this. I have also checked https://community.powerbi.com/t5/Desktop/DAX-Calculation-Percent-of-First-or-Max-Value/td-p/242535 but couldn't get the answers from there working..
Hope to hear from you!
Thanks,
Bas
Solved! Go to Solution.
@KamphuisB,
Add an index column in your table in Query Editor of Power BI Desktop.
Then create the following columns in your table.
Step/step1 = DIVIDE(Table[Count], CALCULATE(SUM(Table[Count]),FILTER(Table,Table[Steps]="A")))
Step/previous step = var previous= CALCULATE(FIRSTNONBLANK(Table[Count],Table[Count]),FILTER(Table,Table[Index]=EARLIER(Table[Index])-1) ) return IF(ISBLANK(previous),1, divide(Table[Count],previous))
Regards,
Lydia
@KamphuisB,
Add an index column in your table in Query Editor of Power BI Desktop.
Then create the following columns in your table.
Step/step1 = DIVIDE(Table[Count], CALCULATE(SUM(Table[Count]),FILTER(Table,Table[Steps]="A")))
Step/previous step = var previous= CALCULATE(FIRSTNONBLANK(Table[Count],Table[Count]),FILTER(Table,Table[Index]=EARLIER(Table[Index])-1) ) return IF(ISBLANK(previous),1, divide(Table[Count],previous))
Regards,
Lydia
H Lydia @v-yuezhe-msft,
Thank you for your response! Step/Step1 didn't work right away, but i was able to fix it by adding ALLSELECTED().
Step/Pstep does not work, because my dataset looks a bit different and i forgot to mention that. Sorry about that.
My data set is like this: (... are other columns or rows)
… | Date | Page | Order (conditional column) | Count |
01/01/2016 | … | … | … | |
01/01/2016 | page | 10000 | 5678 | |
01/01/2016 | step1 | 10 | 70 | |
01/01/2016 | step2 | 20 | 48 | |
01/01/2016 | page | 10000 | 4321 | |
01/01/2016 | page | 10000 | 1234 | |
01/01/2016 | step3 | 30 | 46 | |
01/01/2016 | page | 10000 | 9887 | |
01/01/2016 | step4 | 40 | 34 | |
01/01/2016 | page | 10000 | 347834 | |
01/01/2016 | step5 | 50 | 26 | |
01/01/2016 | page | 10000 | 6493 | |
02/01/2016 | … | … | … |
As followed for the example in my first post:
Like i told at the start of this post: Step/step1 is working:
Step/Step1 = DIVIDE(SUM(Table[Count], CALCULATE([SUM(Table[Count],FILTER(ALLSELECTED(Table),Table[Steps] = "A)))
The formula Step / Previous Step is not working for the visualisation in the first post and the above dataset. First i think there is an typo in your calculation, correct? Cause this was not possible.
IF(ISBLANK(previous),1, divide(Table[Count],previous))
I have changed it to
Step/pStep =
var previous =
CALCULATE(FIRSTNONBLANK(Table[Count],Table[Count),FILTER(Table,Table[Index]=EARLIER(Table[Index)-1))
return
IF(ISBLANK(previous),DIVIDE(SUM(Table[Count]),previous))
But then i get a error that EARLIER is not possible because it refers to an earlier row context which doesn't exist. I have changed EARLIER() to MAX() but that didn't work as well.
Appreciate your help!
Kind regards,
Bas Kamphuis
@KamphuisB,
Please firstly add an index column in your table. Then right click your table and select "New Column" to apply the DAX formula.
Regards,
Lydia
Sorry but step/pstep did not work as a calculated column in my case.. Last weeks i was on holiday so i couldn't answer on this ticket. I will look again into this matter and try it somehow with a calculated metric.
kind regards,
Bas Kamphuis
@KamphuisB,
I have sent your modified PBIX file via Private Message.
Regards,
Lydia
It's ok, that you sent private message,
but what is the solution?
Hi @analyticsmsk ,
Indeed I should have placed the final answer here. It has been a while but I recreated the scenario for you.
In my previous reply you can see that the dataset contains pages in between so you are not able to do a EARLIER or MIN(INDEX) - 1 because it could also be index - 3 etc.
There was only 1 solution for me in which I needed to add a conditional order column based on the steps(pages) that I have in my dataset. So I did stepA = 10, stepB = 20, etc
So based on the example the dataset looks like this:
With that placed I created the following calculated metric:
Step/pStep_adjusted = var previous = CALCULATE(FIRSTNONBLANK('Table'[Count];'Table'[Count]);FILTER(ALLSELECTED('Table');'Table'[Order (conditional column)]=MIN('Table'[Order (conditional column)])-10)) return IF(DIVIDE(SUM('Table'[Count]);previous)=BLANK();1;DIVIDE(SUM('Table'[Count]);previous))
In order to have the data that I needed:
Kind regards,
Bas Kamphuis
@KamphuisB,
Please share your PBIX file via Private Message if the DAX still don't work.
Regards,
Lydia
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |