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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

Create Dax Measure to show column category values as Percentages of Row Total in Matrix Table

Hi All, 

 

I am creating a Metrix visual from a data table that shows the date and daily count of children in different type of foster care placement (3 columns/fields: date, count, placement type). In the screenshot of Metrix visuals below, the top one is the count (sum) of children by year/month and placement type. The bottom one is to show the count as percentage of row totals. Since the Power BI built-in "show value as % as row totals" have 2 decimal points and cannot  be formatted, I need to create a dax measure to have the % values. I created one based on a posting in this forum which recommends using the formulae:

     PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])

see https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6a296f4c-...

 

LijunChen_0-1671481932912.png

However, as shown in the screenshot above, the row percentage values are right only for the Year 2022 total, but not for the monthly vlaues. The monthly %s are not row %, but as % of column total. I would like to have a dynamic % that shows all monthly and yearly values as % of row totals. But the formulae I used cannot do this. 

Can you suggest any dax formulae that I can use to achieve the task?

Thanks. 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , This will based on grand total

 

PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])

 

If you need by year

 

 

PERCENT =divide( SUM([TOTAL SALES]) , calculate(SUM([TOTAL SALES]), removefilters(Table[Month]) ) )

 

 

You can also use isinscope to get different subtotals

DAX- ISINSCOPE: https://youtu.be/Tu92J_OtUK0

Percent of Total and Percent of Subtotal: https://youtu.be/9JTO1s0KV2U

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Anonymous
Not applicable

Many thanks. I will check this out and see.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , This will based on grand total

 

PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])

 

If you need by year

 

 

PERCENT =divide( SUM([TOTAL SALES]) , calculate(SUM([TOTAL SALES]), removefilters(Table[Month]) ) )

 

 

You can also use isinscope to get different subtotals

DAX- ISINSCOPE: https://youtu.be/Tu92J_OtUK0

Percent of Total and Percent of Subtotal: https://youtu.be/9JTO1s0KV2U

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Many thanks. I will check this out and see.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors
Top Kudoed Authors