Help calculating percentage of total

[JennaExe]

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?

[JennaExe]

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? 

[johnt75]

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

[JennaExe]

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: 

[v-zhengdxu-msft]

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.

[JennaExe · Accepted solution]

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])

),

0)

[JennaExe]

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

[johnt75]

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.

[johnt75]

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
)