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
AndrejZitnay
Post Patron
Post Patron

Calculation issue due to interaction

 

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

1 ACCEPTED 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? Smiley Happy

Rate =
[Measure Renewals related]
    / CALCULATE ( [Settlements], ALL ( 'tab B'[DateColumn] ) )

Regards

View solution in original post

12 REPLIES 12
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

 

Capture.JPG

 

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? Smiley Happy

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

SivaMani
Resident Rockstar
Resident Rockstar

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

 

%.JPG

 

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

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.