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 all, I have been struggling with this percentage calculation problem.
My table called Timesheet has four columns: "date", "Level", "A", "Duration" . Level and A are hierarchy, Level contains A. I wanted to show the percentage of total in a given time window(the date slicer).
I made a measure called percentage using:
However, if I use the slider, meaning I am filtering the date column, the percentage in A adds up to the percentage of the grand total, of the whole dataset.
I want the percentage in A to add up to 41.52% in this case, the percentage of the selected level of the filtered total.
Please help!!! I tried soooo many methods, none of them worked.
Solved! Go to Solution.
@Anonymous
Apologies for not attaching the file. See if this one works
Proud to be a Super User!
Paul on Linkedin.
I found perfect what you indicated step by step for a table.
How can I make it possible for me to have the same thing in sight in the same way with a card?
It happens to me that when I want to select something I need that also in the card reflects the percentage of the accumulated of that period.
For example I selected the first row and another object in my view but the card is still at 100%.
What kind of formulation can I use for a card to follow that dynamism hooked to display and date ranges.
How is you rmodel set up? Can you share a sample PBIX file?
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Try:
1) Sum Duration = SUM(Timesheet[Duration])
2) Selected rows = CALCULATE([Sum Duration], ALLSELECTED(Timesheet)
3) Percentage = DIVIDE([Sum Duration], [Selected Rows])
Here is the equivalent using a dummy dataset:
Using:
Sum of Sales = SUM('Sales'[Actuals])
Selected rows = CALCULATE([Sum of Sales], ALLSELECTED('Sales'))
Percentage over total = DIVIDE([Sum of Sales], [Selected rows])
(Just note that in my dummy model I'm using a Calendar table for the date slicer, though I don't think it should make a difference, but...)
Proud to be a Super User!
Paul on Linkedin.
It shows as the same as the grand total column in my post. Is it possible because I didn't make a Hierarchy? Level and A are hierarchical but I need to separate them into two visuals since I have four levels in total and I simplified the data.
@Anonymous
Is the example with my dummy data what you are expecting?
I'm not sure what you mean by the hierarchies. In my example Item is a child to Channel (so channel contains item values)
Proud to be a Super User!
Paul on Linkedin.
Your data is slightly different. I uploaded my revised data. Feel free to play around.
https://drive.google.com/file/d/1DhijmuHeGKpynmGONq58P4dhwlvVGAQ_/view?usp=sharing
@Anonymous
See if any of these fulfill your needs: The top matrix has both level of hierarchies; the bottom matrix only level B:
If not, which is the number you need? (you could do a mockup in Excel)
I'm attaching the PBIX file
Proud to be a Super User!
Paul on Linkedin.
Thanks!
The "percentage sel rows" without level A filter is what I want.
When I select one of the levels in level A, is it possible to have the percentage add up to the percentage of filtered-date total of that level? 50.23% in this case.
@Anonymous
Ok, see if this is what you need:
(Beware I've added dimension tables and used them in visuals and measures)
Proud to be a Super User!
Paul on Linkedin.
It is very close! Sorry I might not be clear enough. Tried to figure it out by myself on your file, still don't know how to. Please see my table based on the screenshot scenario.
Hi, @Anonymous
Can PaulDBrown’s output help you to meet your requirement now?
If so, would you like to mark his reply as a solution so that others can learn from it too?
Thanks in advance!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
How about now?
Revised file attached
Proud to be a Super User!
Paul on Linkedin.
Amazing! Looks exactly like what I want. Just I don't think you attached the file. Can you please? Thanks!
@Anonymous
Apologies for not attaching the file. See if this one works
Proud to be a Super User!
Paul on Linkedin.
Thanks for sharing @PaulDBrown, what is the diffence between the "Pecentage Sel Row" and the "IFINSCOPE" measures?
Thanks in advance.
@Anonymous
Can you try the following formula and see if it works?
Percentage = DIVIDE(Timesheet[Duration], SUMX(VALUES( Timesheet[A] ), CALCULATE(SUM(Timesheet[Duration]),ALLSELECTED(Timesheet))))
OR this formula
% Share =
VAR Volume =
SUM(Timesheet[Duration]
VAR AllVolume =
CALCULATE ( SUM(Timesheet[Duration],ALL('Timesheet'[A] ) )
RETURN
DIVIDE ( Volume, AllVolume )
[A] is the field on the left hand side of the table
Thanks first!
First formula: it's getting worse.
Second formula: %share adds up tp 100%. It is the same as % grand total by default.
@Anonymous
Could you share the file with us? Please hide any sensitive data
Please see this, I made a similar dashboard, the data structure is the same, the column names are slightly different but more clear.
https://drive.google.com/file/d/1DhijmuHeGKpynmGONq58P4dhwlvVGAQ_/view?usp=sharing
@Anonymous
Try this formula.
Seems to be working.
Percentage = DIVIDE(SUM(Worksheet[Duration]),SUMX(VALUES( Worksheet[Level A] ),CALCULATE(SUM(Worksheet[Duration]),ALL(Worksheet))))
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |