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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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%

 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!

1 ACCEPTED SOLUTION
Frequent Visitor

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])))

17 REPLIES 17
Frequent Visitor

Hi Ryan,

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

Super User

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

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

Proud to be a Super User!

Frequent Visitor

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.

Super User

try to create a measure

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

pls see the attachment below

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

Proud to be a Super User!

Frequent Visitor

Thanks for your update Ryan!

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

Super User

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!

Super User

pls try this

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

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

Proud to be a Super User!

Frequent Visitor

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

Super User

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!

Frequent Visitor

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!

Super User

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!

Frequent Visitor

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])))

Super User

you are welcome. glad to hear that

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

Proud to be a Super User!

Frequent Visitor

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!

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!

Frequent Visitor

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.

Super User

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors