topic Re: DAX for Percentage of Column Total in DAX Commands and Tips

DAX for Percentage of Column Total

Anonymous — Sat, 18 Apr 2020 16:45:07 GMT

Hi, I have a question about calculate percentage of column total.
I want to create a bar chart with pecentage in columns like following:

1. When I select "All Selected" in Slicer(Supplier), the chart should be like this,

2. But the idea behind this chart is actually

3. And when I click on a supplier (Here Supplier 4 is selected). This is the final chart I'm trying to get.

The dataset is as following:

I only need to calculate items which "Relevant=yes". So I wrote:
Sales=caculate(sum('table'[sales of market(mEUR)],filter(table,table[relevant]="yes")
Sales%=table.Sales/Calculate(table[Sales]),All())*100
I don't know what went wrong, when I create the chart the percentages in columns are incorrect. (Because I also build pie charts for comparing) .
Could anyone help me with this?
Thank you!

Re: DAX for Percentage of Column Total

Greg_Deckler — Thu, 16 Apr 2020 12:29:47 GMT

Click the little drop down arrow in the Fields area for the column/measure in question and then Show As and then Percent of Column Total.

Re: DAX for Percentage of Column Total

Anonymous — Thu, 16 Apr 2020 12:40:17 GMT

Hi, thank you for replying,

But there is only one "Percent of grand total". It's not correct either....

Re: DAX for Percentage of Column Total

Motasem_Yakhola — Fri, 17 Apr 2020 16:25:47 GMT

Hello Gyarr,

You have to use ALL function inside FILTER function at Sales% measure like this one:

Sales% = table.Sales/Calculate(sum('table'[sales of market(mEUR)]),filter(All(table),table[relevant]="yes"))

Absolutly you have to put the relevant filter to be "yes" in Sales% measures too

Hope that helps you.

Please let me know if you need further support.

----------------------------------------------

Did I answer your question? Mark my post as a solution!

Re: DAX for Percentage of Column Total

Anonymous — Fri, 17 Apr 2020 14:45:40 GMT

Hi Motasem,

Thank you for answering! I think this gonna solve my problem!

What if, my column "relevant" is in another table (Table2))?

I tried it in this way

Sales% = table.Sales/Calculate(sum('table'[sales of market(mEUR)]),filter(All(table2),table2[relevant]="yes"))

It shows wrong. How do I fix the filter here? P.S. The two tables are connected already.

Thank you in advance!

Re: DAX for Percentage of Column Total

Motasem_Yakhola — Fri, 17 Apr 2020 16:45:38 GMT

Hello Anonymous

You have to make a relationship between the two tables, here is an example to fake data generated to answer your question:

Based on that relation ship i go the result accurate 100%, the pie chart show the percantegas based on sales measure

and the column chart show the result of sales% measure

Hope that helps you,

If you still have a problem please share table headers and the created relation ships between them.

Best wishes

----------------------------------------------

Did I answer your question? Mark my post as a solution!

Re: DAX for Percentage of Column Total

Anonymous — Fri, 17 Apr 2020 19:37:58 GMT

Hello! Motasem，

Thank you, I will share more information.

I have a Table1:

And a Table2:

These tables are different Excel files and both had been loaded to Power BI. In the Table2, the values of Product Name are unique, so I could create a relationship between Table1 and Table2:

From: Table1(Product Name) to Table2 (Product Name)

Then I want to create a chart as the picture in the original quesition.

I only want to calculate sales of which products are "relevant", so I have these formulas already:

Sales=Calculate(Sum(Table1[Sales of Market (mEUR)], Filter(Table2, Table2[Relevant]="yes")) which works well and correctly.

then by adapting what you have suggested to get the colunm total in a bar chart:

Sales%=Table1[Sales]/Calculate(Sum(Table1[Sales of Market (mEUR)], Filter(All(Table2), Table2[Relevant]="yes"))*100

In order to create the bar chart, I used:

Axis: Customer;

Legend: Calendar year;

Value: Sales%

Combined with a slicer: Supplier, in order to see Sales% of relevant "yes" of a single suppier for customers.

However the chart goes like this, and when I use my "Slicer: Supplier" to select a single supplier, it remains unchanged with all columns still show 100%.

Thank you for your help and looking forward to get the solution from you!

Best,

Re: DAX for Percentage of Column Total

Motasem_Yakhola — Sat, 18 Apr 2020 11:11:01 GMT

Hello Anonymous,

You need to do the below:

1. Change relation ship filter direction to be both.

2. Change the measure to use ALLSelected instead of All to keep the slicer data:

Sales%=Table1[Sales]/Calculate(Sum(Table1[Sales of Market (mEUR)], Filter(AllSelected(Table2), Table2[Relevant]="yes"))*100

Hope that solve the issue.

----------------------------------------------

Did I answer your question? Mark my post as a solution!

Don't forget to like my post please.

Re: DAX for Percentage of Column Total

Anonymous — Sat, 18 Apr 2020 12:23:30 GMT

Hi Motasem!

Thanks a lot!

Yes,a I have changed the Cross filter direction to be "Both."

Unfortunately it still doesn't work.

I tried this one Sales%=Table1[Sales]/Calculate(Sum(Table1[Sales of Market (mEUR)], Filter(AllSelected(Table2), Table2[Relevant]="yes"))*100

When I select a single supplier with the slicer, I can see the X-Axis(Customer) is changing but not the columns. They still all show 100%. : ( Don't know why...

Best,

Re: DAX for Percentage of Column Total

az38 — Sat, 18 Apr 2020 13:46:15 GMT

Anonymous

have you tried to see digits in table visual, not in chart?

Re: DAX for Percentage of Column Total

MFelix — Sat, 18 Apr 2020 14:24:51 GMT

Hi Anonymous ,

Not really sure if I understand the request you want but having made a pie chart with:

Legend: Customer
Details: Calendar Year
Values: Sales

I get some percentages based on customer then add a slicer for supplier.

Created the following measure:

Sales % = SUM ( Table1[Sales of Marker(mEUR)] ) / CALCULATE ( SUM ( Table1[Sales of Marker(mEUR)] ); ALL ( Table1[Customer]; Table1[Calendar Year] ) )

As you can see in the image below the percentages of the bar chart match the ones in the pie chart, not really sure if this is what you need but please take a look.

I have place the Relevant filter on page filter so it would be easier to have it accross all charts at once but you can use it as you have on the filter of measure itself.

If this is not the final result please share some more detailed expected outcome with a table showing exactly what is the percentage you want calculated per bar.

Re: DAX for Percentage of Column Total

Anonymous — Sat, 18 Apr 2020 14:32:06 GMT

Hi az38,

If I write like this and check the numbers in table visual, it shows as following.

Sales=caculate(sum('table'[sales of market(mEUR)],filter(table,table[relevant]="yes")
Sales%=Table1[Sales]/Calculate(Sum(Table1[Sales of Market (mEUR)], Filter(AllSelected(Table2), Table2[Relevant]="yes"))*100

Thank you for replying!

Re: DAX for Percentage of Column Total

Anonymous — Sat, 18 Apr 2020 16:36:17 GMT

Hi Miguel! Thank you for your post!

But I'm not trying to make percentages sum up to 100% horizontally.

I'd like to reframe what I"m trying to achieve exactly. : )

So when I select "All suppliers" in the slicer, the chart should be look like this.

But the idea behind it is actually like below (each supplier has a share in 1 customer's business).

Then when I select 1 supplier (Supplier 4), the chart changes into:

So this final chart is the one I'm trying hard to get. Every time I select a supplier, it will show its share in different customers'business.

I shared my data tables are in the original quesiton and also in other reviews, hope you can find them and me further suggestions.

Thank you a lot!!! : )

Best,

Re: DAX for Percentage of Column Total

MFelix — Sat, 18 Apr 2020 17:06:35 GMT

HI Anonymous ,

On your information there was not enough data to have more than one supplier by customer so I added a few rows of the same client but different supplier and values.

As you can see below the Customer TT has 3 values no the 100% stacked column (I made this chart for comparision) using the measure below you can see that the top chart calculates accordingly:

Sales % = SUM(Table1[Sales of Marker(mEUR)])/CALCULATE(SUM(Table1[Sales of Marker(mEUR)]);ALL(Table1[Supplier]))

The top chart is a bar chart with following format:

X-Axis: Customer
Values: %Sales
Legend: Year

Once again I have made the calculation of relevant in the filter but can be added to your calculation measure.

If you want to have different colours according to selection of supplier you need to create a Measure to make the conditional formatting of the colour.

Re: DAX for Percentage of Column Total

Anonymous — Sat, 18 Apr 2020 20:14:37 GMT

Hi Miguel!

Really thank you for your post!

It works!!!!

Then do you know if I still want to put the "Relevant=Yes" Filter inside the formula, what should I do?

THANK YOU!! : )

Best,

Gyarrr

Re: DAX for Percentage of Column Total

MFelix — Sat, 18 Apr 2020 20:33:15 GMT

Hi Anonymous,

Should be similar to this

Sales % =
CALCULATE(
SUM ( Table1[Sales of Marker(mEUR)] )
/ CALCULATE (
SUM ( Table1[Sales of Marker(mEUR)] );
ALL ( Table1[Customer]; Table1[Calendar Year] )
);
Table2[Relevant] = "yes")