Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
micang
Regular Visitor

% with static denominator

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

 

2 ACCEPTED SOLUTIONS
sjoerdvn
Super User
Super User

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)

View solution in original post

v-sdhruv
Community Support
Community Support

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

View solution in original post

6 REPLIES 6
v-sdhruv
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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
Super User
Super User

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
Regular Visitor

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
Super User
Super User

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)

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.