cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aanmi Regular Visitor
Regular Visitor

How to show percentage column in matrix

Hi all

 

Need your help on matrix visualization. 

I would like to show percentage of each store as below

The formula percentage for each product should be = (product qty / total)*100

 

I want it to show like table on the right as the result

 

Anyone can help me ?

 

pbi.png

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: How to show percentage column in matrix

Hi @aanmi

 

Did you change the format settings of the measure to have 0 decimal points as per the arrows in the image I posted?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

6 REPLIES 6
aanmi Regular Visitor
Regular Visitor

Re: How to show percentage column in matrix

It;s ok guys

i got it 

 

but may i know how to do percentage formatting?

 

Means, how can I ensure the percentage only show rounded percentage (without any decimal place)

 

E.g.

0.54%  should display as 1%

14.87% should display as 15%

Phil_Seamark Super Contributor
Super Contributor

Re: How to show percentage column in matrix

Hi @aanmi

 

Maybe create a DAX calcuated measure that you drag to your matrix which you have full control over the formatting for.

Here is a sample pattern for what the measure might look like:

 

Store A Percent = 
VAR RowTotal = SUM([Store A])
VAR GrandTotal = CALCULATE(SUM(Table2[Store A]),ALLEXCEPT('Table2',Table2[Store A]))
RETURN DIVIDE(RowTotal,GrandTotal,0)

You can then format the measure using the formatting options on the toolbar as per this picture.

 

perecent.png

 

I would suggest you unpivot your table so that the stores are not in columns in their own right and look more like the picture below .

 

unpivot2.png

 

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: How to show percentage column in matrix

Hi again @aanmi

 

If you do unpivot, your DAX measure might look like this

 

Store Percent = 
VAR RowTotal = SUM([Value])
VAR GrandTotal = CALCULATE(SUM(Table2[Value]),ALLEXCEPT('Table2',Table2[Attribute]))
RETURN DIVIDE(RowTotal,GrandTotal,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aanmi Regular Visitor
Regular Visitor

Re: How to show percentage column in matrix

hi @Phil_Seamark

 

My measure formula look like this:

 

% = var bigTotal = SUMX(all(STOCK_Stationary[Product]),calculate (sum(STOCK_Stationary[Store]))) return divide (sum(STOCK_Stationary[Story])*100,bigTotal, 0)

 

but unfortunately the percentage still have decimal points

Phil_Seamark Super Contributor
Super Contributor

Re: How to show percentage column in matrix

Hi @aanmi

 

Did you change the format settings of the measure to have 0 decimal points as per the arrows in the image I posted?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aanmi Regular Visitor
Regular Visitor

Re: How to show percentage column in matrix

hi @Phil_Seamark

 

thank you...i just know measure value can also be format use modelling function.

It works now