Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Assume I have two tables:
1. DimSupplier
2. FactInvoice
Now I would like to show the marketshare of the biggest/max supplier (based on SUM of Spend) compared to others. (If no filter, supplier 1 is the biggest supplier with total spend 290)
How I can do in DAX? The biggest suppliers will be changed based on the filter from users.
Solved! Go to Solution.
I would use a Card visualization for that and put Supplier in there. Then, set a Top N filter (1) on that bad boy (Supplier) with a value of the Sum of Spend.
Create this measure:
Top Supplier = MAXX(SUMMARIZE(Suppliers,Suppliers[Supplier],"MySpend",SUM([Spend])),[MySpend])
Then create this measure:
Others = SUM(Suppliers[Spend]) - [Top Supplier]
Add these two measurs as values in your pie chart visualization.
I would use a Card visualization for that and put Supplier in there. Then, set a Top N filter (1) on that bad boy (Supplier) with a value of the Sum of Spend.
Assume I have 2 two tables:
1. DimSupplier
2. FactInvoice
I tried to create New Table based on Group By with the code:
New Table = GROUPBY(FactInvoice, DimSupplier[Name],
"TotalSpend", SUMX(CURRENTGROUP(), FactInvoice[Spend]))
The new table after creating:
But this new table is not dynamically filtered when user do filtering using slicer. So assume if only Supplier 1 and Supplier 2 are choosen. The table should only show data for Supplier 1 and Supplier 2.
Is this possible?
You shouldn't need to create a table, just create a table visualization and put Supplier from DimSupplier into it as well as Spend with a SUM aggregation.
I have posted my real problem in here:
https://community.powerbi.com/t5/Desktop/How-to-show-MAX-compared-to-Others/m-p/174881#M76420
My initial purpose is to create `table measure` in order to solve the above problem
Got it, I just answered that one!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |