Hi,
This has been vexing me and my team for days.
We're using Direct query and writing our DAX in Visual Studio.
This makes us use DAX much more than we'd like to and makes grouping difficult...
There's a fact table that looks something like this (though with many more columns and customers):
(Revenue_Per_Ton is a calculated column)
Each customer can appear several times, each time with a different date or parameter in different columns.
The end result should be fairly simple:
The customers should be ranked (TOPN) based on the sum of their [Tons].
1. a basic Power BI card with the total [revenue_per_ton] for the top 2 customers.
2. another card showing the same total for all the rest except the top 2 customers.
i.e. if customer A had a total of 10 tons, B 7 tons and C 5 tons, I need one card to show the sum of all A [revenue_per_ton] + sum of all B [revenue_per_ton]. The second card will show [revenue_per_ton] for all, minus (A+B)*
*in the example it's only C, but I actually have 15K+ customers.
I tried creating DAX measure to create a GROUPBY table to even just sum all the Tons, but received an error:
Tons table= Groupby( Transactions, Tranactions[Customer], "Total Ton", SUMX( Currentgroup(), Transactions[Ton] )
Thought I'd start from there, see if it works and then add the sum of [Revenue_per_Ton], then maybe rank it, but it's hopeless.
Hours spent reading Microsoft Docs and watching YouTube guides failed to explain how to do so...
Is there a way to do it? perhaps not with Groupby?
Using Matrix object in Power BI I can somehow do it, but then I can't insert only the top 2 into a card...
Thank you!
