topic Re: Need a Dax to return % of top product in DAX Commands and Tips

Need a Dax to return % of top product

Anonymous — Fri, 16 Jul 2021 14:27:05 GMT

Hello,

I am putting together some dynamic text and need a Dax that will return the % that the top supplier makes up of total.

In the example below, I would want DAX to return 50%.

I need to show is 50% i.e. (25+25)/100 in the example below since supplier D is the highest of them all.

Supplier	Amount
A	20
B	15
C	15
D	25
D	25

Total	100

I do have following measure in my report if that helps:

Actual Amount = ROUND(SUM('Transactions'[Amount]),2)

Re: Need a Dax to return % of top product

timg — Fri, 16 Jul 2021 14:02:35 GMT

Hi zzakir,

Is this the result you're looking for? It takes the % value of the top 1 supplier based on the [% of total] column.

TESTMEASURE = CALCULATE(SELECTEDVALUE(Test[% of Total]), TOPN(1,ALL(Test), Test[% of Total],DESC))

Best regards,

Tim

Re: Need a Dax to return % of top product

Anonymous — Fri, 16 Jul 2021 14:17:02 GMT

Hello timg,
This is close to what I want. Perhaps I should've asked my original question differently:

I have a transaction table below.

I need to show is 50% i.e. (25+25)/100 in the example below in a card like you had 67% above.

Supplier	Amount
A	20
B	15
C	15
D	25
D	25

Total	100

I do have following measure in my report if that helps:

Actual Amount = ROUND(SUM('Transactions'[Amount]),2)

Re: Need a Dax to return % of top product

Samarth_18 — Fri, 16 Jul 2021 14:18:21 GMT

Hi Anonymous ,

If i am taking it correctly you just need a highest % of total. You can take a Max of "% of total column" column.

Measure = MAX(Supplier_data[% of Total])

Please let me know if this is the output you looking.

Re: Need a Dax to return % of top product

Anonymous — Fri, 16 Jul 2021 14:26:26 GMT

Samarth_18 I have editted my original question to be clearer.

I do not have % column.

Re: Need a Dax to return % of top product

Samarth_18 — Fri, 16 Jul 2021 15:13:35 GMT

You can create a measure with below code

Measure = var total_sum = CALCULATE(SUM(Supplier_data[Amount]),ALL(Supplier_data)) var getting_max = MAXX(SUMMARIZE(ALL(Supplier_data),Supplier_data[Supplier],Supplier_data[Amount],"Max_Value",SUM(Supplier_data[Amount])/total_sum),[Max_Value]) return FORMAT(getting_max,"0%")

Re: Need a Dax to return % of top product

Anonymous — Fri, 16 Jul 2021 17:01:17 GMT

This was helpful. I had to tweak the formula a little. Here was the final formula:

% of highest Supplier = var total_supplier = CALCULATE([Actual Amount],'Find Suppliers - Addl Info'[Supplier]<>BLANK()) var getting_max = MAXX(SUMMARIZE(FILTER('Fact Table','Fact Table'[Supplier ID]<>BLANK()),'Find Suppliers - Addl Info'[Supplier],"Max_Value",SUM('Fact Table'[GL Amount])/total_supplier),[Max_Value]) return FORMAT(getting_max,"0.0%")