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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LijunChen
Resolver I
Resolver I

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

@LijunChen , 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

 

View solution in original post

Many thanks. I will check this out and see.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@LijunChen , 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

 

Many thanks. I will check this out and see.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors