Percentage of Row Total for line and clustered column chart

[harirao]

Hi All,
Need to create a line and clustered column chart by using % of Row Total
created two measures for for Dependent & Independent as follows
Dependent = CALCULATE(SUM(Unpivot_Table[Value]), FILTER(Unpivot_Table,
Unpivot_Table[Measure] = "Dependent"))
Independent = CALCULATE(SUM(Unpivot_Table[Value]), FILTER(Unpivot_Table,
Unpivot_Table[Measure] = "Independent "))
By using these two measure for line and clustered column chart, but in Show value as getting only % of Grand Total
below snapshot for reference


Same logic i used in Excel Pivot giving correct result

Can you assist me on this how to create % of Row Total for line and clustered column chart.

Thank you

[harirao]

Hi @Anonymous (Rena),
Thanks for sharing the solution, which is working perfectly.

Regards,
Hari

[harirao]

Hi @Anonymous ,

Please find the below link to access the sample data of pbix file as well as excel, the solution which i am looking for.
https://drive.google.com/drive/folders/14-6F9xYto2Ea3qWR4H70G96z1bEFg4vc?usp=sharing

I have mentioned detailed explanation on page-1.

Thank you

[Anonymous · Accepted solution]

Hi @harirao ,

As checked your shared pbix file, there is Sales measure besides "Independent BACKLOG"and "Dependent BACKLOG". So I updated your sample pbix file, please find the details in the attachment.

Dependent BACKLOG % of Row Total = 
DIVIDE(
    [Dependent BACKLOG],
    CALCULATE(
        SUM('Unpivot-Sample-Table'[Value]),
       FILTER('Unpivot-Sample-Table','Unpivot-Sample-Table'[Measure] IN {"Independent BACKLOG","Dependent BACKLOG"}) // ALL('Unpivot-Sample-Table'[Measure])
    )
)

Independent BACKLOG % of Row Total = 
DIVIDE(
    [Independent BACKLOG],
    CALCULATE(
        SUM('Unpivot-Sample-Table'[Value]),
       FILTER('Unpivot-Sample-Table','Unpivot-Sample-Table'[Measure] IN {"Independent BACKLOG","Dependent BACKLOG"})// ALL('Unpivot-Sample-Table'[Measure])
    )
)

Best Regards

[gmsamborn]

Hi @harirao 

The reason your percentages don't add up is because besides 'Dependent BACKLOG' and 'Independent BACKLOG' values for [Measure], there are also rows with a value of 'Sales'.

Your denominator should reflect this.

See Page 2 of Sample_%_Row_Total - mine.pbix

Would this work?

_Dependent BACKLOG % of Row Total = 
DIVIDE(
    [Dependent BACKLOG],
    CALCULATE(
        SUM('Unpivot-Sample-Table'[Value]),
        'Unpivot-Sample-Table'[Measure] IN { "Dependent BACKLOG", "Independent BACKLOG" }
    )
)

[harirao]

Hi @Anonymous 

I tried using below logic, still getting different % Row Total

below snapshot for your reference

Dependent Order Actuals RSD % of Row Total =

DIVIDE(

    [Dependent Order Actuals RSD],

    CALCULATE(

        SUM(Unpivot_Table[Value]),

        ALL(Unpivot_Table[Measure])

    )

)

Independent Order Actuals RSD % of Row Total =

DIVIDE(

    [Independent Order Actuals RSD],

    CALCULATE(

        SUM(Unpivot_Table[Value]),

        ALL(Unpivot_Table[Measure])

    )

)

But i am looking for below solution as is in Excel 

Can you help me on this

Thank you

[Anonymous]

Hi @harirao ,

Could you please provide some raw data in the table 'Unpivot_Table' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi @harirao ,

You can create another two new measures as below and put it on the line and column chart to replace the original measures [Dependent] and [Independent]:

%GT Dependent =
VAR _all =
    CALCULATE (
        SUM ( Unpivot_Table[Value] ),
        ALLEXCEPT ( Unpivot_Table, Unpivot_Table[Attribute] )
    )
RETURN
    DIVIDE ( [Dependent], _all )

%GT Independent =
VAR _all =
    CALCULATE (
        SUM ( Unpivot_Table[Value] ),
        ALLEXCEPT ( Unpivot_Table, Unpivot_Table[Attribute] )
    )
RETURN
    DIVIDE ( [Independent], _all )

Best Regards