100% stacked column graph on unpivot data by filtering some rows but keeping value in total

[ramandeep_saini]

In bleow example data table, how to filter out Key='Success' in 100% stacked column chart but keeping those value for calculation of  denominator value. Desired graph attached. I tried example in excel but need to final thing with lots of data in powerBI.

Data

Date	Key	Value
1/04/2022	Success	100
1/04/2022	Error1	10
1/04/2022	Error2	5
2/04/2022	Success	200
2/04/2022	Error1	15
2/04/2022	Error2	10

Expected results:

Date	Key	Value	%Value
1/04/2022	Error1	10	8.70%
1/04/2022	Error2	5	4.35%
2/04/2022	Error1	15	6.67%
2/04/2022	Error2	10	4.44%

[ramandeep_saini]

@Ashish_Mathur : Thanks a lot. your solution solved my problem 🙂

[Ashish_Mathur]

You are welcome.

[AlexisOlson]

You just need to define an appropriate measure to calculate the percentage you want.

Try something like this:

Percent =
DIVIDE (
    CALCULATE ( SUM ( Table1[Value] ), KEEPFILTERS ( Table1[Key] <> "Success" ) ),
    CALCULATE ( SUM ( Table1[Value] ), ALLSELECTED ( Table1 ) )
)

[ramandeep_saini]

@AlexisOlson : Thanks for quick advise. Your solution is definitly doing what i need but only problem is it's calculating results based on all dates and i need to calculate results sepratly for each day. how to tell fix that measure to seprate(in SQL langauage say group by) calculation for each day(or month or year base on what date group i need) 

[Ashish_Mathur]

Hi,

Please show the expected result very clearly in a simple Table.  Once we get the correct figures there, we can always switch the visual to any other.

[ramandeep_saini]

@Ashish_Mathur : Added expected result in orig post

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  You should create a stacked chart (not a 100% stacked chart)

Hope this helps.