Required to calculate percentage for Clustered Col...

rama211976 · ‎12-07-2022

Hi All,

I need to calculate the percentage (not the GT) for the report with multiple filter conditions. I could able to resolve this when having only one filter. the main requirement is the filter conditions should be considered as 100% and do the calculation but my logic considering the GT and getting the percentage wrong...

I have columns like

State Gender age group salary range marriage status

In this, I need to calculate the percentage in the clustered column chart using state and marriage status as x and y axis

Filers are State Gender age group salary range

sample data shared below

Gender	Age Group	Status	Region	Salary
Male	15-24	UnMarried	City 1	Lower I
Female	15-24	UnMarried	City 2	Middle I
Male	15-24	Divorce	City 1	Lower I
Male	15-24	UnMarried	City 3	Middle II
Female	15-24	UnMarried	City 2	Lower I
Female	15-24	Married	City 1	Lower II
Male	15-24	UnMarried	City 3	Lower I
Female	15-24	UnMarried	City 1	Lower I
Male	15-24	UnMarried	City 1	Lower I
Female	25-34	UnMarried	City 1	Lower I
Female	25-34	UnMarried	City 1	Lower II
Male	25-34	UnMarried	City 4	Middle II
Male	25-34	UnMarried	City 4	Middle II
Female	25-34	UnMarried	City 1	Middle II
Male	25-34	UnMarried	City 4	Lower I
Female	25-34	UnMarried	City 4	Middle II
Female	25-34	UnMarried	City 1	Middle I
Male	25-34	UnMarried	City 1	Lower I
Male	25-34	UnMarried	City 1	Middle II
Female	25-34	UnMarried	City 3	Lower I
Female	25-34	UnMarried	City 1	Lower I
Female	25-34	Married	City 5	Middle I
Female	25-34	UnMarried	City 1	Upper II
Male	25-34	UnMarried	City 3	Lower I
Male	25-34	UnMarried	City 5	Lower I
Male	25-34	UnMarried	City 1	Lower II
Male	25-34	UnMarried	City 1	Middle II
Male	25-34	UnMarried	City 1	Middle II

Anonymous · ‎12-07-2022

Hi @rama211976 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Percentage = 
VAR _allrcount =
    CALCULATE (
        COUNT ( 'Table'[Status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Region] = SELECTEDVALUE ( 'Table'[Region] ) )
    )
VAR _selcount =
    CALCULATE ( COUNT ( 'Table'[Status] ) )
RETURN
    DIVIDE ( _selcount, _allrcount )

If the above one can't help you get the desired result, please provide more raw 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. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

rama211976 · ‎12-11-2022

My requirement whenever we do filter automatically it needs to calculate 100%.. sharing the excel file for better understanding

https://drive.google.com/drive/u/0/folders/1EspAR9KFIjIArh7ZaPG7Pa0jDVGs699F

Anonymous · ‎12-12-2022

Hi @rama211976 ,

It seems that I have no access to your shared file, could you please grant me the proper permission to it? And please provide the expected result with the specific examples and explanations. Thank you.

Best Regards