Forum Discussion
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
MaggieCommunity 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
- lc_finance
Solution Sage
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
- AnonymousNot 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
Community 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
MaggieCommunity 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.