Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
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
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.
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.
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.
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).
Hi, @Anonymous
Thank you for your feedback.
Please check the link down below, whether it is what you are looking for.
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.
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)
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
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
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
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
@Anonymous , Try a measure like
divide(countrows(Table),calculate(countrows(Table), all(Table)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |