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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PERCENTAGE CALCULATION WITH FILTERS

Hi,

 

I'm trying to find percentage of a column value, with a slicer for date range and another slicer for another column (with values say A,B,C).

When the date range slicer is modified, the denominator and numerator for the number of rows for percentage must change accordingly, whereas if A or B or C are selected only the numerator must change and not the denominator (since I want all the rows from that date range irrespective of A,B,C while calculating percentage).

I tried using CALCULATE with ALL, FILTER but it is not giving the expected denominator.

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Please use the following measure:

 

Measure = VAR allvalue = CALCULATE(COUNT('data'[ID]),ALL('data'[Type])) VAR county = CALCULATE(COUNT('data'[ID]),FILTER('data','data'[Choice] = "Yes")) return DIVIDE(county,allvalue)

Capture1.PNG

 

Please refer ot the pbix file.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

12 REPLIES 12
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

All measures are in the sample pbix file.

 

Picture9.png

 

Qty total =
SUM(Sales[Qty])
 
Denominator =
CALCULATE( [Qty total], ALLSELECTED( Dates), ALL( Types))
 
Percentage =
DIVIDE( [Qty total], [Denominator])
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Thank you for your response but I'll be more clear with my requirement. I need to first filter out the rows that have "Yes" and then find it's percentage with respect to total number of rows (originally 5/9 = 55.5%) and when the date slicer is used to modify dates I consider the rows within that date range (say 11-01 to 22-02 then it's 3/4 = 75%) and then a type option can be chosen (say B, then it's 1/4 = 25%, notice that the numerator changes but not the denominator). 

Slicer  .png

Hi, @Anonymous 

Thank you for your feedback.

Please check the link down below, whether it is what you are looking for.

 

Picture2.png

 

https://www.dropbox.com/s/e688e87l32khfwo/mspv2.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Thanks again, technically Count All = CALCULATE(COUNTROWS(data),ALL(data[type])) should have worked but when I use it in my file the count all is reducing when type is selected. That is the reason I couldn't figure it out. Using CALCULATE, ALL and FILTER in different combinations didn't work.

Hi @Anonymous ,

 

Please use the following measure:

 

Measure = VAR allvalue = CALCULATE(COUNT('data'[ID]),ALL('data'[Type])) VAR county = CALCULATE(COUNT('data'[ID]),FILTER('data','data'[Choice] = "Yes")) return DIVIDE(county,allvalue)

Capture1.PNG

 

Please refer ot the pbix file.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

Anonymous
Not applicable

Thank you for your response but I've come to understand that both my filters are from the same table and thus somehow not working as expected. I tried creating another table for the dates but a relation isn't established by default between these two tables and even when I created a relation the date slicer wasn't having any impact at all. So it looks like the slicers are having an impact only when they're from the same table but at the same time there shouldn't be more than one slicer from the same table. How do I resolve it?

Hi @Anonymous ,

 

As in my pbix file, create a new dim_date table and create a one to mant realtionship between dim_date table to the fact table.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Anonymous
Not applicable

I did create a relationship and when I modified the date slicer after adding the relation all the cards displayed the output as (Blank). Why is this the case?

Hi @Anonymous ,

 

Would you please check if the date column in your fact table is date type instead of date/time type. If it is date/time type, try to create a new date column and create the relationship by it.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Anonymous
Not applicable

Thanks but why shouldn't the type be date if the original type is of date/time? If I maintain it in date/time I have a lot of rows and converting them to date is the only way to properly remove the duplicates leading to a drastic reduction in the number of rows which are used only for a slicer eventually requiring only one occurence of each date.

Also I noticed that the table with the relationship didn't work when I created it with a DAX query but worked when created using PowerQuery. Why is that so?

Hi @Anonymous ,

 

It's by design in power bi desktop. And I suggest you create a new date column instead of convert the original column to the date type.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

amitchandak
Super User
Super User

@Anonymous , Try a measure like

 

divide(countrows(Table),calculate(countrows(Table), all(Table)))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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