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'm trying to create the Pareto chart based on multiple slicers.
I have two columns for slicing. They are named "Area" and "Exp Category". And I have the "USD" column. It contains amounts I want to calculate and show in my Pareto chart.
It works fine for slicing by Area field if I use this formula to calculate Rank of Amounts:
Rank CC Area = RANKX(ALLSELECTED(Data[Area]); CALCULATE(SUM(Data[USD]); ALLEXCEPT(Data; Data[Area])))
But I want to add the second slicer by "Exp Category".
I added slicer by "Exp Category" field and then I added the "Exp Category" field to ALLEXCEPT section of formula:
Rank CC Area = RANKX(ALLSELECTED(Data[Area]); CALCULATE(SUM(Data[USD]); ALLEXCEPT(Data; Data[Area]; Data[Exp Category])))
But it calculates strange values:
I guess it happens because I should replace ALLSELECTED(Data[Area]) with something. It must be anything like ALLSELECTED(ALLEXCEPT(Data[Area]; Data[Area]; Data[Exp Category])). But it does not work.
My pbix file is here.
I'll be grateful for any help.
Thanks in advance,
Alexander.
Hi @rcyber
Try adjusting the Rank formula as follows
Rank CC Area = RANKX ( SUMMARIZE ( ALLSELECTED ( Data ), Data[Area], Data[Exp Category] ), CALCULATE ( SUM ( Data[USD] ), ALLEXCEPT ( Data, Data[Area], Data[Exp Category] ) ) )
And Cumulative Area as follows
Cumulative Area Total = VAR rr = [Rank CC Area] RETURN ( CALCULATE ( SUM ( Data[USD] ), FILTER ( SUMMARIZE ( ALLSELECTED ( data ), Data[Area], Data[Exp Category] ), [Rank CC Area] <= rr ) ) )
Thanks a lot for quick help.
It works good and calculates rank correctly.
But it's not exactly what I want co create.
The SUMMARIZE function calculates subtotals with filtering by Data[Area] and Data[Exp Categories] fields.
But I want to calculate (and rank next) subtotals grouping by Data[Area] and filtering Data[Area] and Data[Exp Categories] both.
I want to create the report like this:
I hope you understand what I mean.
Thanks in advance,
Alexander.
Hi @rcyber
My apologies for late reply...
Please try this revised MEASURE.
File attached here as well
Rank CC Area = RANKX ( ALLSELECTED ( Data[Area] ), CALCULATE ( SUM ( Data[USD] ) ), , DESC, DENSE )
Cumulative Area Total = VAR rr = [Rank CC Area] RETURN ( CALCULATE ( SUM ( Data[USD] ), FILTER ( ALLSELECTED ( Data[Area] ), [Rank CC Area] <= rr ) ) )
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |