Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cuongle
Advocate II
Advocate II

How to show MAX compared to Others using DAX

Assume I have two tables:

 

1. DimSupplier

 

supplier.PNG

 

2. FactInvoice

 

FactInvoice.PNG

 

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)

 

MaxSpend.png

 

How I can do in DAX? The biggest suppliers will be changed based on the filter from users.

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler, is there anyway that the name of biggest supplier can show up in the pie chart

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
cuongle
Advocate II
Advocate II

Assume I have 2 two tables:

 

1. DimSupplier

 

supplier.PNG

 

2. FactInvoice

 

FactInvoice.PNG

 

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:

 

New Table.PNG

 

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.