Forum Discussion

rcyber's avatar
rcyber
Resolver I
8 years ago

Dynamic rank issue

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.

 

 

Need Help

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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] )
    )
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      rcyber

       

      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
            )
        )
    )
      • rcyber's avatar
        rcyber
        Resolver I
        8 years ago

        Zubair_Muhammad,

        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.