Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI experts.
I have little issue with one measurement.
Rate = [Measure Renewals related]/[Settlements]
Is saying that 1624 / 218 is 26.36%
It should be actually 13.42%
I have there some interaction conditions and calculation is
picking up 827 / 218 is 26.36%
I have two date filters on numerator and denominator and in one occasion is interaction blocked as it must be that way.
Is there measurement for actual % so measurement will ignore interaction?
Thank you.
Kind regards.
Andrej
Solved! Go to Solution.
Hi @AndrejZitnay,
Thanks for the detailed explanation. Now I understand it totally. And I believe it you can use ALL or ALLExcept function to get the expected result in this scenario. However, without the sample data for testing, it's hard to provide a tested and correct formula for you. So could you try the formula below to see if it works?
Rate = [Measure Renewals related] / CALCULATE ( [Settlements], ALL ( 'tab B'[DateColumn] ) )
Regards
Hi @AndrejZitnay,
I have two date filters on numerator and denominator and in one occasion is interaction blocked as it must be that way.
Is there measurement for actual % so measurement will ignore interaction?
If I understand you correctly, you should be able to use ALL function which can ignore any filters that might have been applied in your scenario. The formula below is for your reference.
Rate = CALCULATE ( [Measure Renewals related], ALL ( Table[DateColumn] ) ) / [Settlements]
Note: you need to replace Table[DateColumn] with the real table and column name that you're using as Slicers on your report.
Regards
Hello @v-ljerr-msft
Thank you for your respond.
I will try to make it more clear.
I have two date slicer on my measure.
1st Date of renewals which have all active interaction
2nd Retention Renwals which have one blocekd interaction as settlements shouldn't change.
Both Measures for Renewals & Settlements comes from excel tab A (symple count if formula)
Slicer Date of Renewals is Date table for excel tab A & excel tab B
Slicer Retention Periods is date from excel tab B
Many thanks in advance for your kind help.
Kind regards.
Andrej
Hi @AndrejZitnay,
Thanks for the detailed explanation. Now I understand it totally. And I believe it you can use ALL or ALLExcept function to get the expected result in this scenario. However, without the sample data for testing, it's hard to provide a tested and correct formula for you. So could you try the formula below to see if it works?
Rate = [Measure Renewals related] / CALCULATE ( [Settlements], ALL ( 'tab B'[DateColumn] ) )
Regards
Hello @v-ljerr-msft
OMG!!!
You are star.
Now my measure works perfectly!!!
Thank you very much.
Kind regards.
Andrej
HI @AndrejZitnay,
What is your requirement?
It would be helpful if you attach snap shots
Regards,
Siva
Here it is. I need to get 13.42% out of measurment between Related Renewals & Settlements
How did you calculate Retention Rate?
I mean the formula if you have used DAX
Rate = [Measure Renewals related]/[Settlements]
I got it.
Will you be able to share your sample pbix or sample data?
Hello Siva,
It is hard to give you sample data but I get both figures as measurment with counting rows in excel.
I am sure that problem is with Edit Interaction.
You can see on my first picutre that it is off on first screenshot.
I need jsut get right calculation.
My current formula is taking into considiration interaction betwen slicers.
Andrej
@AndrejZitnay I have found the issue.
When you disable the interaction, it will apply only on the visual. But the value selected in the slicer will filter your data i.e, the measure calculates the count from the filtered table.
Let's say,
I have a Table called Table A(has 365 rows) with the date. I added the date field to the slicer. As from your report, I have rows count as a measure called count and added in a KPI. If select 01/01/2017 to 28/02/2017.
If I select 01/01/2017 to 28/02/2017, the measure calculates 59. But KPI will show 365 if the interaction is off.
I hope I made it clear
Regards,
Siva
Hello @SivaMani
It is clear however I need to get calculation right with current set up of slicers.
I have complex dashboard.
Everything is working and it is only unfortunate that one calculation doesn't work.
I was hoping that there is same trick to get around that.
thanks.
Andrej
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |