cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculation issue due to interaction

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
Highlighted
Solution Sage
Solution Sage

Re: Calculation issue due to interaction

HI @AndrejZitnay,

 

What is your requirement?

 

It would be helpful if you attach snap shots

 

Regards,

Siva

Highlighted
Helper V
Helper V

Re: Calculation issue due to interaction

Here it is. I need to get 13.42% out of measurment between Related Renewals & Settlements

 

%.JPG

 

Highlighted
Solution Sage
Solution Sage

Re: Calculation issue due to interaction

How did you calculate Retention Rate?

 

I mean the formula if you have used DAX

 

Highlighted
Helper V
Helper V

Re: Calculation issue due to interaction

Rate = [Measure Renewals related]/[Settlements]

Highlighted
Solution Sage
Solution Sage

Re: Calculation issue due to interaction

I got it.

 

Will you be able to share your sample pbix or sample data?

Highlighted
Helper V
Helper V

Re: Calculation issue due to interaction

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

Highlighted
Solution Sage
Solution Sage

Re: Calculation issue due to interaction

@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

 

Highlighted
Helper V
Helper V

Re: Calculation issue due to interaction

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

Highlighted
Microsoft
Microsoft

Re: Calculation issue due to interaction

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

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors