cancel
Showing results for
Did you mean:
Highlighted
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

## Re: Calculation issue due to interaction

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

12 REPLIES 12
Highlighted
Solution Sage

## Re: Calculation issue due to interaction

It would be helpful if you attach snap shots

Regards,

Siva

Highlighted
Helper V

## Re: Calculation issue due to interaction

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

Highlighted
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

## Re: Calculation issue due to interaction

Rate = [Measure Renewals related]/[Settlements]

Highlighted
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

## 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

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

## 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

## Re: Calculation issue due to interaction

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