topic % with static denominator in DAX Commands and Tips

% with static denominator

micang — Wed, 30 Apr 2025 18:47:54 GMT

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

Re: % with static denominator

Sahir_Maharaj — Wed, 30 Apr 2025 20:14:38 GMT

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)

Re: % with static denominator

micang — Wed, 30 Apr 2025 20:55:32 GMT

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

Re: % with static denominator

sjoerdvn — Thu, 01 May 2025 08:45:36 GMT

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)

Re: % with static denominator

v-sdhruv — Sun, 04 May 2025 07:36:57 GMT

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

Re: % with static denominator

v-sdhruv — Wed, 07 May 2025 11:00:44 GMT

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

Re: % with static denominator

v-sdhruv — Tue, 20 May 2025 10:32:39 GMT