How to write this Formula in Power BI for the combination Thank you!

[Keithszeto]

Hi everyone, 

I'm a beginner for the Power BI, I have to do the graphic report for my manager, but I have zero knowledge to get the formula, i would like to showing the result as below, 

If selected the Description 

1. "Good" , the amount = 100 
2. Precetage (Test Result / Amount) x100%  and sum the precetage 
   • Example: Description "Fail",  ID 1:  (1 / 20) x 100% = 5%  , ID 2: (10/80) x 100% = 12.5%, ID 3: (15/50) x 100% =30%
   • Sum Total "Fail" precetage = 47.5% 

ID	Amount	Description	Test Result
1	20	Fail	1
1	0	Good	1
1	0		0
1	0		0
1	0		0
1	0		0
1	0		0
1	0		0
1	0		0
1	0		0
2	80	Excellent	1
2	0	Fail	10
2	0	Good	2
2	0	Bad	20
2	0		0
2	0		0
2	0		0
2	0		0
2	0		0
2	0		0
3	50	Fail	15
3	0	Bad	2
3	0	Excellent	1
3	0		0
3	0		0
3	0		0
3	0		0
3	0		0
3	0		0
3	0		0

Thank you for your assist! 

[Keithszeto]

Hi Ryan, 

Samp-test.pbix 

Sample-Test-Data 

Sorry for my late response! 🙂 

Yes, based on ID of first row amount, the other same ID  from row 2 to row 10 must be the same amount, no change. 

Based on the sample Data as below

Here is the info-grphic report as below, if drop down menu selected all, the values is perfect. 

However, if selected "Good" in Description, 

Precetnage will become 0.00% as no Amount value at the row., Sum of amount aslo diplay "Zero) 

Actually, I can input the amount on each row, but the total amount will not be accurate. 

Now the Amount total  150 which is correct. If adding the amount at each row, the total amount will become 1500 

[ryan_mayu]

@Keithszeto 

you can try to create a new amount column

amount2 = if('Table'[Description]<>"",MAXX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Amount]<>0),'Table'[Amount]))

then you replace the amount column with the new one

[Keithszeto]

Thanks for your update Ryan! 

- The total amount increased if using amount2.  (Total amount need showing 150. Then, selecting "Good" to be showing the amount is "100" ) or 

- Amount: Can we showing the amount total only and hide the other amount if select "good" in the  drop down menu? 

- Amount2: Hide the total amount and showing amount only if select "Good" in drop down menu? 

- The total Precetnage divided by Amount2 also difference. 

[ryan_mayu]

try to create a measure

Measure = if(HASONEVALUE('Table'[Description]),sum('Table'[amount2]),sum('Table'[Amount]))

pls see the attachment below

[Keithszeto]

Thanks for your update Ryan! 

If selected more than 1 description, the values is not correct. Can we fix it? Thanks 

[ryan_mayu]

what's the expected output when you select good and bad? 

then when shall we display total? select good bad excellent and fail?

[ryan_mayu]

@Keithszeto 

pls try this

Measure = if(HASONEVALUE('Table'[Description]),sum('Table'[amount2]),CALCULATE(sum('Table'[Amount]),REMOVEFILTERS('Table'[Description])))

[Keithszeto]

Hi Ryan, 

Thanks for your formula and almost done! 

According to the "Description", If I selected "Bad" & "Excellent" and suppose the measure is total 130 based on the sample Database , how come will showing 150? 

Thanks 

[ryan_mayu]

why bad and excellent returns 130? could you pls clarify the calculation logic?

[Keithszeto]

For my less knowledge, I thought that "Bad" and "Excellent" not showing up in ID1, and believe  the Amount return should be 130 based on Amount 2. 

Any suggestions ? Thank you! 

[ryan_mayu]

yes, i agree, but we have both excellent and bad for id 2 and 3. So in my solution , the output is 260.

so far, i can't provide the better solution. Let's see if anyone else can help you on this.

[Keithszeto · Accepted solution]

Hi Ryan, 

Thank you so much for your assist! 

I found the solution and it can fix my issue. 

Amount Total = sumx(Values('Table'[ID]), calculate(max('Table'[Amount])))

[ryan_mayu]

you are welcome. glad to hear that

[Keithszeto]

Hi Ryan,

I would like to show the total amount while multiple selecting whatever fail, bad, excellent, good. Thank you so much for your assist! 

[ryan_mayu]

what do you mean by good amount is 100?

why the fail for ID 2 is 10/80? the amount for ID2 is 0 for the "fail" row

[Keithszeto]

Hi Ryan, 

Sorry for my poor English! 

Only the each first row ID showing the amount, and I would like to write the formula to caculate the each first row ID amount. 

If Description selected "Good",  sum the "amount" in each first row ID. 

ID 1: 20

ID 2: 80

In each same ID, the amount are same, I can put the amount "20" on each same ID row, but the amount total will be difference . I have no experience how to fix it, thanks for your help Ryan! 🙂 

Example: ID 1, Amount total 20 only, if i place "20" into other 9 row in ID 1, the total amount will showing 200 in the graphic report. 

ID	Amount	Description	Test Result
1	20	Fail	1
1	20	Good	1

1	20		0
1	20		0
1	20		0

The total amount will become 100. but actually the total ID amount 20 only. 

[ryan_mayu]

let me clarify this. if we select "Good" , then we sum the amount in  each first row ID

what about this

In each same ID, the amount are same, I can put the amount "20" on each same ID row, but the amount total will be difference .

you only add up the amount which is the same as first row? will we have 10 or 30 for ID1?

pls update your sample data which may include more scenarios. Then we can provide a better solution for you.