cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aanmi
Frequent 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
Microsoft Phil_Seamark
Microsoft

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!

View solution in original post

6 REPLIES 6
aanmi
Frequent 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%

Microsoft Phil_Seamark
Microsoft

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!

Microsoft Phil_Seamark
Microsoft

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

Microsoft Phil_Seamark
Microsoft

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!

View solution in original post

aanmi
Frequent 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

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors