% with static denominator

[micang]

Hi All,

I have a simply power bi report.

It consistes of a table and 6 slicers.

5 of the slicers are simple dropdowns of columns (purely to filter the table)

1 of the slicers is a date slicer to filter by date. (this date column is in the same table  - all columns are in the same table)

What I am trying to do is the following:

Whatever the period the date slice is on (for example lets say 01/01/2025 to 01/31/2025 is selected). In the table there is a column "referral number", the distinct count of this column (for the period selected above)  = 100. Now, what I am trying to do is that when any of the other 5 slicers are used, I would like to get the count of that filtered content (also the distinct count of "referral number" and divide that by the 100 to get the %. So lets say if slice one A is a country and USA is picked and the amount for USA is 20, then I need 20/100. NOw if the user keeps usa sliced and then moves on to the 2nd slicer which lets say is City and picks NY and the value for NY is 10, then I need 10/100 and so forth.

I am unable to keep the date filter static - I have tried ALL, ALLexcept, removefilter but no matter what I try, the "100"  keeps on changing. I have spent hours on this and tried so many measures but I just cannot get it to work.

Any help or guidance would be greatly appreciated on this.

Thanks

[sjoerdvn · Accepted solution]

Try VALUES instead of KEEPFILTER

Referral % = 
VAR CurrentCount = DISTINCTCOUNT(Data[referral number])
VAR BaseCount =
    CALCULATE(
        DISTINCTCOUNT(Data[referral number]),
        ALL(Data),
        VALUES(Data[Date])
    )
RETURN
DIVIDE(CurrentCount, BaseCount)

[v-sdhruv]

Hi @micang ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @micang ,
You can try this approach after using the measure shared by @Sahir_Maharaj 
 Create a Filtered measure-
 FilteredReferralCount =
DISTINCTCOUNT('Data'[Referral Number])
This will respect all the slicers.
Then create the final measure-

ReferralSharePct =
DIVIDE([FilteredReferralCount], [BaseReferralCount], 0)
Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @micang ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[sjoerdvn · Accepted solution]

Try VALUES instead of KEEPFILTER

Referral % = 
VAR CurrentCount = DISTINCTCOUNT(Data[referral number])
VAR BaseCount =
    CALCULATE(
        DISTINCTCOUNT(Data[referral number]),
        ALL(Data),
        VALUES(Data[Date])
    )
RETURN
DIVIDE(CurrentCount, BaseCount)

[micang]

Hi @Sahir_Maharaj 

This is so close, appreciate your time - the calculations are correct, but only if there is no filtering on the date slicer.

Example, lets say the total referral number distinct count of the whole dataset is 1000, so what it's doing now, when other slicers are selected, its using 1000 as the denominator, irrespective what the date slicer is on. So if I have the data slicer not filtered and have no other slicers filterered, then the % is 100, whichis correct (1000/1000). If I then slicer on country, and country (USA) is 50, then % shows  5, which is correct (50/1000) - but now, as soon as i use the data slicer and lets say i choose 1 month and, its still using the denominator of 1000 and not the distinct count of that month.

Thank you so much for your effort, this is he closest I have gotten after hours and hours of trying.

Thanks

micang

[Sahir_Maharaj]

Hello @micang,

Could you please try this approach:

Referral % = 
VAR CurrentCount = DISTINCTCOUNT(Data[referral number])
VAR BaseCount =
    CALCULATE(
        DISTINCTCOUNT(Data[referral number]),
        ALL(Data),
        KEEPFILTERS(Data[Date])
    )
RETURN
DIVIDE(CurrentCount, BaseCount)