Dynamic rank issue

[rcyber]

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.

[Zubair_Muhammad]

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]

@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]

@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.

[Zubair_Muhammad]

Hi @rcyber

I will look into it and get back to you

[rcyber]

Hi Zubair_Muhammad,

Do you have any ideas about my question?

Best regards,
Alexander.

[Zubair_Muhammad]

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 )
        )
    )