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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AnSc93
Helper I
Helper I

DAX measure for percentage share

Hi together,

 

I have a simple example where I need to calculate the percentage share based on different aggregation levels but I don't know how to do this with a DAX Measure.
The data looks like this:

AnSc93_0-1723015748452.png

The first table in the top is the initial data. In one location is a division, for example manufacturing, and has a certain number of employees.
I would like to calculate
1. The percentage of employees for each division and each location (see left table)

2. The percentage of employees for all divisions at all locations (see right table)


Could any one tell me, how to write this within a DAX-Measure?

Thank you in advance.

Regards,

Andreas

2 ACCEPTED SOLUTIONS

Hi,

Thank you very much for your message, and I am not sure where the country is coming from.

Perhaps, you can try to replace ALL with ALLSELECTED DAX function.

 

ALLSELECTED function (DAX) - DAX | Microsoft Learn

 

share: =
VAR _employee = [employee:]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( location[location] ) && ISINSCOPE ( division[division] )
            || ISINSCOPE ( location[location] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( division[division] ) )
            ),
        DIVIDE ( _employee, CALCULATE ( [employee:], ALLSELECTED () ) )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

v-fenling-msft
Community Support
Community Support

Hi, @AnSc93  

Thanks for Jihwan_Kim's concern about this issue.

His idea of a solution that I agree with.

 

I'd like to ask:

What exactly does your semantic model look like? 

What are your form fields like? What kind of results are you looking for in the end? 

 

I added the Country field and a few rows of data to the dataset based on the solution provided by Jihwan_Kim, but I'm not sure if it's consistent with the structure of your dataset: 

vfenlingmsft_0-1723108057232.png

 

Perhaps you need to create another new Measure to perform the calculations: 

vfenlingmsft_1-1723108057235.png

 

 

share1 = 
VAR _employee = [employee:]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( location[location] ) && ISINSCOPE ( division[division] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( division[division] ) )
            ),
        ISINSCOPE ( location[location] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( location[location] ) )
            ),
        DIVIDE ( _employee, CALCULATE ( [employee:], ALLSELECTED () ) )
    )

 

 

Choose a different Measure in a different visual, here is my test result: 

vfenlingmsft_3-1723108143695.png

 

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you. 

 

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-fenling-msft
Community Support
Community Support

Hi, @AnSc93  

Thanks for Jihwan_Kim's concern about this issue.

His idea of a solution that I agree with.

 

I'd like to ask:

What exactly does your semantic model look like? 

What are your form fields like? What kind of results are you looking for in the end? 

 

I added the Country field and a few rows of data to the dataset based on the solution provided by Jihwan_Kim, but I'm not sure if it's consistent with the structure of your dataset: 

vfenlingmsft_0-1723108057232.png

 

Perhaps you need to create another new Measure to perform the calculations: 

vfenlingmsft_1-1723108057235.png

 

 

share1 = 
VAR _employee = [employee:]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( location[location] ) && ISINSCOPE ( division[division] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( division[division] ) )
            ),
        ISINSCOPE ( location[location] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( location[location] ) )
            ),
        DIVIDE ( _employee, CALCULATE ( [employee:], ALLSELECTED () ) )
    )

 

 

Choose a different Measure in a different visual, here is my test result: 

vfenlingmsft_3-1723108143695.png

 

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you. 

 

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AnSc93
Helper I
Helper I

Hi @Jihwan_Kim ,

 

thank you for your support. I have the same data model as you provided.

It's almost working.

Unfortunately I have the use-case that I need to pre-filter the report for some specific user groups.

So the table location has the column country inside and there is the prefilter on:

AnSc93_0-1723032169229.png


So the issue with that is that the 100 % level is shifting, depending on the filtered country.

Is there a way how to consider this filter within the measures? (So that each filtered country is considered as 100 %)

Thanks again.

Regards,

Andreas

Hi,

Thank you very much for your message, and I am not sure where the country is coming from.

Perhaps, you can try to replace ALL with ALLSELECTED DAX function.

 

ALLSELECTED function (DAX) - DAX | Microsoft Learn

 

share: =
VAR _employee = [employee:]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( location[location] ) && ISINSCOPE ( division[division] )
            || ISINSCOPE ( location[location] ),
            DIVIDE (
                _employee,
                CALCULATE ( [employee:], ALLSELECTED ( division[division] ) )
            ),
        DIVIDE ( _employee, CALCULATE ( [employee:], ALLSELECTED () ) )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_2-1723018759697.png

 

 

Jihwan_Kim_0-1723018721792.png

 

 

share: = 
VAR _employee = [employee:]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( location[location] ) && ISINSCOPE ( division[division] )
            || ISINSCOPE ( location[location] ), DIVIDE ( _employee, CALCULATE ( [employee:], ALL ( division[division] ) ) ),
        DIVIDE ( _employee, CALCULATE ( [employee:], ALL () ) )
    )

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors