Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rankx and Sum at grouped level

Hi everyone,

 

I have a table with those columns:
CompanyCode;Year, Currency, product, SalesAmount,client

I am trying to Rank the companies by SalesAmount, summed at the level CompanyCode-Year-Currency, but still sensitive to other fields filters on the reports (ex: products)

I tried this calculated column:

Rank_SalesAmount = CALCULATE(
RANKX('Data';
sum('Data'[SalesAmount]);sum('Data'[SalesAmount]);DESC; Dense );
filter('Data'; 'Data'[CompanyCode] = EARLIER('Data'[CompanyCode]) && 'Data'[Year] = EARLIER('Data'[Year])&& 'Data'[Currency] = EARLIER('Data'[Currency])))

But it doesn't work. The context doesn't apply to the sum, every company is ranked 1.

then this :

Rank SalesAmount=  CALCULATE(
RANKX('Data';
sumx(filter('Data'; 'Data'[CompanyCode] = EARLIER('Data'[CompanyCode]) && 'Data'[Year] = EARLIER('Data'[Year])&& 'Data'[Currency] = EARLIER('Data'[Currency]));'Data'[SalesAmount]);
;DESC; Dense );
filter('Data'; 'Data'[CompanyCode] = EARLIER('Data'[CompanyCode]) && 'Data'[Year] = EARLIER('Data'[Year])&& 'Data'[Currency] = EARLIER('Data'[Currency])))

I get this error: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist"

 

Do you know how to get the right context in order to sum and rank at the right level ?

 

I am not using ALLEXCEPT measure on purpose because it forbid to filter on sublevels.

 

Thank you.

  • Hi Anonymous 

    Create measures

    Measure =
    CALCULATE (
        SUM ( Sheet5[SalesAmount] ),
        FILTER (
            ALLSELECTED ( Sheet5 ),
            Sheet5[CompanyCode] = MAX ( Sheet5[CompanyCode] )
                && Sheet5[Year] = MAX ( Sheet5[Year] )
                && Sheet5[Currency] = MAX ( Sheet5[Currency] )
        )
    )
    
    Measure 2 = RANKX(ALLSELECTED(Sheet5),[Measure],,DESC,Dense)
    

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • HI Anonymous ,

     

     

    Can you share a sample Power BI file?

    You can share it via DropBox, One Drive, Drive or another similar tool.

     

    Regards,

     

    LC

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi lc_finance 

      No sorry i can't. It's from the company i work for nd kind of confidential.

      Do you have an idea ?

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous 

    Create measures

    Measure =
    CALCULATE (
        SUM ( Sheet5[SalesAmount] ),
        FILTER (
            ALLSELECTED ( Sheet5 ),
            Sheet5[CompanyCode] = MAX ( Sheet5[CompanyCode] )
                && Sheet5[Year] = MAX ( Sheet5[Year] )
                && Sheet5[Currency] = MAX ( Sheet5[Currency] )
        )
    )
    
    Measure 2 = RANKX(ALLSELECTED(Sheet5),[Measure],,DESC,Dense)
    

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.