March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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!
Solved! Go to Solution.
Hi Ryan,
Thank you so much for your assist!
I found the solution and it can fix my issue.
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
you can try to create a new amount column
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.
Thanks for your update Ryan!
If selected more than 1 description, the values is not correct. Can we fix it? Thanks
what's the expected output when you select good and bad?
then when shall we display total? select good bad excellent and fail?
Proud to be a Super User!
pls try this
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?
Thanks
why bad and excellent returns 130? could you pls clarify the calculation logic?
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!
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.
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.
you are welcome. glad to hear that
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!
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
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 | 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.
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |