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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jackofall
Resolver I
Resolver I

New Measure to show % of total

I am probably going about this the wrong way.

Currently I have SQL-code that works well showing a Count of 'Exit Reasons' in a database. I can use that code of the bat in a Power BI Query and get a nice Pie Chart. Pie chart automagicly creates Count of 'Exit Reasons' in Visual level filters.Nice Pie chart of the bat.Nice Pie chart of the bat.

Next to it I want to use a Table including the same data. Chosing the same Field in the Table however does not create a Count of 'Exit Reasons'. So I created a Measure New Measure = SUM('Query1'[Amount]). This one gives me a count per reason from my SQL-code.

Amount per 'Exit Reason'Amount per 'Exit Reason'

 

In Report Builder I simply use an Expression to calculate the % of the total for each reason. =Sum(Fields!Amount.Value) / ReportItems!OutTransfersTotSum.Value

I am at loss for how to make this in Dax. What I would like is to change the code in my measure to something like. New Measure = (SUM('Query1'[Amount])/Total(Query1'[Amount])) * 100

1 ACCEPTED SOLUTION

SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))

The syntax is ok but I get 0 on every row and a Total of 1

SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Amount]))

1 on every row and a Total of 1.

 

To add insult to injury I tried to SUM('Query1'[Amount]) / 5. In the hope of getting 671/5, 1/5, 2/5, etc. That works and the grand total checks out. I will fiddle around with the SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))

 

CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons])) returns 10268 instead of the correct sum 5237 so it seems that my Filter is off. I need to remove Blank and a rouge row.

 

ALLSELECTED as you wrote does the trick. So the final touch is to * 100 to get % instead of decimals. The only bit left is that I want to show 2 decimals instead of being rounded down to 0. The total is now 100.

 

Measure = = CALCULATE(SUM('Query1'[Amount])/CALCULATE(SUM ('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reason]))*100)

 

13, 0, 0, 0, 12 .. Total 100

I removed * 100 and Format (). Then I could change to Modeling / Format Percentage and 2 digits.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@Jackofall try doing the next formula, SUM('Query1'[Amount])/CALCULATED(SUM('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reasons]))

SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))

The syntax is ok but I get 0 on every row and a Total of 1

SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Amount]))

1 on every row and a Total of 1.

 

To add insult to injury I tried to SUM('Query1'[Amount]) / 5. In the hope of getting 671/5, 1/5, 2/5, etc. That works and the grand total checks out. I will fiddle around with the SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))

 

CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons])) returns 10268 instead of the correct sum 5237 so it seems that my Filter is off. I need to remove Blank and a rouge row.

 

ALLSELECTED as you wrote does the trick. So the final touch is to * 100 to get % instead of decimals. The only bit left is that I want to show 2 decimals instead of being rounded down to 0. The total is now 100.

 

Measure = = CALCULATE(SUM('Query1'[Amount])/CALCULATE(SUM ('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reason]))*100)

 

13, 0, 0, 0, 12 .. Total 100

I removed * 100 and Format (). Then I could change to Modeling / Format Percentage and 2 digits.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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