Forum Discussion

amaddala's avatar
amaddala
Icon for Helper I rankHelper I
5 years ago
Solved

Percentage mix for multiple dimensions

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.   

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      amaddala
      Icon for Helper I rankHelper I
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.