Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Guyz,
I made a ranking using Rankx based on total sales per client :
Solved! Go to Solution.
Hey @TheChaks,
first I created a simple measure for the column "CA HT", the final measure will reference this base measure. Besides the somewhat shorter writing, it also ensures the implicit Context Transition (from Filter Context to Row Context), this is the base measure:
ms CA HT = SUM(V_Cube[CA HT])
The final measure looks like this:
ms CA HT **bleep** = var rankofcurrentrow = [Rang CA/CLT] return IF( HASONEVALUE('V_Cube'[Client]) ,// one value client GROUPBY( FILTER( ADDCOLUMNS( ALL(V_Cube[Client]) ,"theRank", [Rang CA/CLT] ,"theAmount", [ms CA HT] ) ,[theRank] <= rankofcurrentrow ) ,"value", SUMX(CURRENTGROUP(), [theAmount]) ) ,// total GROUPBY( // FILTER( ADDCOLUMNS( ALL(V_Cube[Client]) ,"theRank", [Rang CA/CLT] ,"theAmount", [ms CA HT] ) // ,[theRank] <= rankofcurrentrow // ) ,"value", SUMX(CURRENTGROUP(), [theAmount]) ) )
It may look like more complex than it actually is, this is just because I repeat almost the same part for the Total Row, this can be much more simplified (at least I'm sure) depending on your requirements and your data model. Here I omitted the filtering, this allows showing the correct value for the Total row.
Nevertheless, using the final measure in a table will create this:
Hopefully, this is what you are looking for!
Regards,
Tom
Hi,
Please share a dataset and show the expected result.
Hey,
Thanks for helping.
Here is what I have:
The ranking you see come from the measure I showed in my previous message.
The expected result is as follow;
Hi,
Write these measures
FYI the data source table is V_CUBE and the sales column is CA_HT
Hey @TheChaks,
first I created a simple measure for the column "CA HT", the final measure will reference this base measure. Besides the somewhat shorter writing, it also ensures the implicit Context Transition (from Filter Context to Row Context), this is the base measure:
ms CA HT = SUM(V_Cube[CA HT])
The final measure looks like this:
ms CA HT **bleep** = var rankofcurrentrow = [Rang CA/CLT] return IF( HASONEVALUE('V_Cube'[Client]) ,// one value client GROUPBY( FILTER( ADDCOLUMNS( ALL(V_Cube[Client]) ,"theRank", [Rang CA/CLT] ,"theAmount", [ms CA HT] ) ,[theRank] <= rankofcurrentrow ) ,"value", SUMX(CURRENTGROUP(), [theAmount]) ) ,// total GROUPBY( // FILTER( ADDCOLUMNS( ALL(V_Cube[Client]) ,"theRank", [Rang CA/CLT] ,"theAmount", [ms CA HT] ) // ,[theRank] <= rankofcurrentrow // ) ,"value", SUMX(CURRENTGROUP(), [theAmount]) ) )
It may look like more complex than it actually is, this is just because I repeat almost the same part for the Total Row, this can be much more simplified (at least I'm sure) depending on your requirements and your data model. Here I omitted the filtering, this allows showing the correct value for the Total row.
Nevertheless, using the final measure in a table will create this:
Hopefully, this is what you are looking for!
Regards,
Tom
Hey Tom !
Your solution works perfect ! Thanks
As a beginner, it's indeed quite difficult for me to understand it all.... I'll do my best 🙂
Is there any other simple way to get the same result?