cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MS_P
Helper I
Helper I

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 @MS_P ,

 

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

Hi, @MS_P 

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/

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

Jihwan_Kim
Community Champion
Community Champion

Hi, @MS_P 

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/

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 @MS_P ,

 

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

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 @MS_P ,

 

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

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 @MS_P ,

 

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

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 @MS_P ,

 

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

@MS_P , Try a measure like

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors