Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Keithszeto
Frequent Visitor

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

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% 

 

IDAmountDescriptionTest Result 
120Fail1
1

0

Good1
10 0
10 0
10 0
10 0
10 0
10 0
10 0
10 0
280Excellent1
20Fail10
20Good2
20Bad20
20 0
20 0
20 0
20 0
20 0
20 0
350Fail15
30Bad2
30Excellent1
30 0
30 0
30 0
30 0
30 0
30 0
30 0

 

Thank you for your assist! 

 

 

1 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])))
 
Keithszeto_0-1708584107737.png

 

View solution in original post

17 REPLIES 17
Keithszeto
Frequent Visitor

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

Keithszeto_0-1708488364097.png

 

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

Keithszeto_5-1708489243455.png

 

 

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 

Keithszeto_3-1708488703304.png

 

 

@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
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

 

 

 

try to create a measure

Measure = if(HASONEVALUE('Table'[Description]),sum('Table'[amount2]),sum('Table'[Amount]))
11.PNG12.PNG
 
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for your update Ryan! 

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

Keithszeto_1-1708512242691.png

 

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Keithszeto 

pls try this

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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? 

Keithszeto_0-1708575214645.png

 

Thanks 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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! 

Keithszeto_0-1708578107586.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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])))
 
Keithszeto_0-1708584107737.png

 

you are welcome. glad to hear that





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 AmountDescriptionTest Result 

1

20Fail1
120Good1

1

20 0
120 0
120 0

 

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

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.