Percentage mix for multiple dimensions

[amaddala]

I am trying to show percentage mix for various dimensions in my visuals. Since I have a lot of items under each category, calculating percentage mix for each of them is not ideal.

For example, the below image visual 1 is Power BI feature that gives % of grand total, what I am trying to achieve is visual 2. 

I am manually achieving this using the DAX query for this. However there are a lot of visuals and lot of categories in my report. So I am trying to find a way to dynamically achieve this.   

[Anonymous · Accepted solution]

Hi @amaddala ,

According to my understanding, you want to calculate the % of each type(NAK,Failure, Success) under each category(2021-w22,2021-w23...), right?

You could use the following formula to create measures:

Sum Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', 'Table'[Cate] = MAX ( 'Table'[Cate] ) )
)

nak Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Type] = "NAK"
                && 'Table'[Cate] = MAX ( 'Table'[Cate] )
        )
    ),
    [Sum Measure],
    0
)

Failure Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Type] = "Failure"
                && 'Table'[Cate] = MAX ( 'Table'[Cate] )
        )
    ),
    [Sum Measure],
    0
)

Success Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Type] = "Success"
                && 'Table'[Cate] = MAX ( 'Table'[Cate] )
        )
    ),
    [Sum Measure],
    0
)

The final output is shown below:

Please take a  look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@amaddala ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

On every measure on the visual, you can right click on the visual in the visualization pane and use % of total

Appreciate your Kudos.

[amaddala]

Updated my question, please let me know if the question is clear