topic Re: Help calculating percentage of total in DAX Commands and Tips

Help calculating percentage of total

JennaExe — Fri, 01 Nov 2024 08:56:21 GMT

Hi, I'm really not good with DAX/measures etc, and I'm sure what I want to do is probably pretty simple, but I can't figure it out!

This is a snip of the dataset I'm working with (it's open source data from the Home Office in the UK):

In the final column that's highlighed is the volume of outcomes. Three columns before that is the Outcome Group, which has 22 different outcomes. There are multiple police forces in the dataset, and several financial quarters. The percentage will only make sense for a single police force for a single quarter. When I use slicers to select a force, and a quarter, I want to be able to display what percentage of the total outcomes, each Outcome Group was.

What formula should I use, please?

Re: Help calculating percentage of total

johnt75 — Fri, 01 Nov 2024 09:53:26 GMT

Try

% of Total = IF ( HASONEVALUE ( 'Table'[Date] ) && HASONEVALUE ( 'Table'[Force Name] ), VAR CurrentOutcomes = SUM ( 'Table'[Outcomes for inv] ) VAR TotalOutcomes = CALCULATE ( SUM ( 'Table'[Outcomes for inv] ), ALLEXCEPT ( 'Table', 'Tabbe'[Date], 'Table'[Force Name] ) ) VAR Result = DIVIDE ( CurrentOutcomes, TotalOutcomes ) RETURN Result )

Re: Help calculating percentage of total

JennaExe — Fri, 01 Nov 2024 10:07:52 GMT

Hi, thanks so much for helping... it doesn't seem to be doing what it should, as it's producing very low percentages and if it's working it should always be out of 100% total... Any suggestions?

Re: Help calculating percentage of total

johnt75 — Fri, 01 Nov 2024 11:47:17 GMT

Can you share the PBIX? You could upload it to Google Drive or OneDrive or something.

Re: Help calculating percentage of total

JennaExe — Fri, 01 Nov 2024 12:27:15 GMT

Ah no, sorry, can't do that as there's other data in there which is sensitive and can't be shared 😞

Re: Help calculating percentage of total

johnt75 — Fri, 01 Nov 2024 13:31:31 GMT

Use Perfomance Analyzer to get the DAX code for the query for the table visual and post the query here. That will tell us any other filters which are being applied and which would affect the calculation.

Re: Help calculating percentage of total

JennaExe — Fri, 01 Nov 2024 13:51:43 GMT

Further to the above, I think the formula you gave me is working to some extent... I tried putting a table on a blank page with no filters, and the formula works if the table contains both the force name and the quarter date:

However, when I try to use the measure in a graph, it isn't showing the correct percentage. In this image, I have a matrix table which I've set to show percentages, which are correct, but the graph using my new measure (I only want to display percentage of Charges) is underneath with incorrect percentages:

Re: Help calculating percentage of total

Anonymous — Mon, 04 Nov 2024 05:24:23 GMT

Hi JennaExe

I did some change on johnt75's dax, please try this:

% of Total = VAR _CurrentDate = SELECTEDVALUE ( 'Tabbe'[Date] ) VAR _currentForce = SELECTEDVALUE ( 'Table'[Force Name] ) RETURN IF ( HASONEVALUE ( 'Table'[Date] ) && HASONEVALUE ( 'Table'[Force Name] ), VAR CurrentOutcomes = SUM ( 'Table'[Outcomes for inv] ) VAR TotalOutcomes = CALCULATE ( SUM ( 'Table'[Outcomes for inv] ), FILTER ( ALLSELECTED ( 'Table' ), 'Tabbe'[Date] = _CurrentDate && 'Table'[Force Name] = _currentForce ) ) VAR Result = DIVIDE ( CurrentOutcomes, TotalOutcomes ) RETURN Result )

Hope this can help,

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Help calculating percentage of total

JennaExe — Mon, 04 Nov 2024 07:53:32 GMT

Hello, thanks for your post. Not sure if that's a solution (I'm assuming there are multiple ways to achieve the same result?), but over the weekend I asked ChatGPT for some help and have come up with the following formula which is working as I want it to:

OutcomePercentage =

DIVIDE(

SUM('Police Outcomes'[Outcomes for investigations closed in the quarter]),

CALCULATE(

SUM('Police Outcomes'[Outcomes for investigations closed in the quarter]),

REMOVEFILTERS('Police Outcomes'[Outcome Group]),

VALUES('Police Outcomes'[Force Name]),

VALUES('Police Outcomes'[Date])