How to create a measure to get % Value in Power BI dax

0
votes

I have a dataset of countries and cases. I have created a measure to get a distinct count of cases per country. Please refer to the image below. I want the % share of each country. I want to create a measure that gives me a total of the count which is shown below. If I'm able to get the total I can divide this current measure of distinct count by the total count. I don't want to use the option where Power BI shows me the value as %of grand total. I want to manually calculate as a measure.

I'm new to PowerBI and DAX if there is any other better way please let me know. Basically what I need is % share of each country after I take their Distinct Count of cases.

enter image description here

1
powerbidax

1 Answers

1
votes

Create a measure with a variable that works out the total cases and divides the country quantity by the total cases. As you do not have numerical count of cases, you'll need to calculate this too

Percentage per Country = 
        
    VAR TotalCases = DISTINCTCOUNT( 'CountryCasesText'[CaseRef] )

    VAR PercentageCases = 
        DIVIDE(
            TotalCases , 
            CALCULATE(
                    DISTINCTCOUNT( 'CountryCasesText'[CaseRef] )
                    ,ALL( 'CountryCasesText' )
                )
        )
RETURN
    PercentageCases

Then you need to format the column as a percentage.

So starting with the dataset on the left, you can work out the chart on right.
Distinct Count Percentage

I have added a measure of Case Count for visual reference only, the count is not needed as the calculation is embedded within the Percentage per Country measure