Forum Discussion
Need Help With Calculations - DAX Maybe?
Hello all. Hope you're doing well and staying safe. I am finally at the last stretch of finishing my dashboard. However, I need help with creating a card that displays a total percentage. I know it's probably confusing but allow me to explain with the table below:
| Operating Unit | Annual Leave Hrs | C Details (Exluding Partners) | NC Details (Including Partners) | C Hrs (Excluding Partners) | C Hrs (Including Partners) | Utilisation % (Excluding Partners) | Utilisation % (Including Partners) |
| A | 2,306 | 6,220 | 6,794 | 13,576 | 13,980 | 69% | 67% |
| B | 668 | 2,269 | 3,040 | 4,894 | 4,894 | 68% | 62% |
| C | 1,862 | 4,610 | 5,933 | 10,688 | 11,040 | 70% | 65% |
| D | 3,271 | 11,202 | 12,155 | 21,209 | 22,072 | 65% | 64% |
| E | 264 | 1,395 | 1,395 | 9,108 | 9,108 | 87% | 87% |
| F | 102 | 1,400 | 2,133 | 663 | 908 | 32% | 30% |
| G | 5,562 | 18,131 | 21,375 | 36,593 | 38,850 | 67% | 65% |
| Grand Total | 14,035 | 45,227 | 52,825 | 96,731 | 100,852 | 68% | 66% |
I have calculated the percentages as well from the grand total. How am I able to use just the "68%" and the "66%" (last line within the grand total) and display it on my card. Is there a method in which I could just extract that number and put it into the card? OR do I calculate from the grand total using DAX and obtain the percentage from there? I do not know much from DAX so any help would be highly appreciated. Thanks!
Thanks.
Hi Anonymous ,
Use the following measure:
Measure = CALCULATE(MAX('Chargeability Detail by OU'[ Utilisation for Chargeability Detail(Include Partners)]),'Chargeability Detail by OU'[ Operating Unit] = "Grand Total")Best Regards,
Dedmon Dai
17 Replies
If the totals in your table are correct, you can just put those same measures (or columns with same aggregation selections) into a card visual (either separate single cards or all into the same multi-card visual.
Pat
Click on an open area on your report page, then click on the Card visual in the visualizations panel. If you have measures created, just drag it to the card. If no, drag the column with those values to the card and choose the appropriate aggregation (probably sum) with the pull-down arrow on that column in the card field well.
Pat
Hello again. I have done what you have advised, however, the card is not showing me a percentage much less the amount that I have caclulated within the Excel document.
I did drag the "Utilization for Chargeability Details (Not Including Partners) into the card and it is showing me this number. If I do change it to a percentage format, it would show me this:
Please advise. What am I doing that is wrong? Thank you again for all of your help. I am sorry that I keep bothering you.
Hello all.
I am sorry for doing this but I am bumping this request for the reason that I really need to complete my dashboard. If there is anyone that could help me with this matter I would really appreciate it. This is the only thing that is pending to be honest.
Thank you all for your help and have a great day.Hi Anonymous ,
What's the last column(Utilisation % (Excluding Partners),Utilisation % (Including Partners) )in your table visual? Just put these two measure or column into card visual.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
It won't allow me to put the percentage. I need 2 cards on the dashboard page. One that represents the % of utlilization including partners and one card without partners. However, when I do try to drag the column onto the card, it would give me this:
Can you see the 4.58?
And even if I convert it into a percentage format, it would give me this:
Also, I used the formula (=E4/(C4+E4) as an example) to find the utlilization percentage.
I would definately mark the issue as resolved once I recieve an answer. Thank you again
Hi Anonymous ,
It's better that you can show us sample pbix file by onedrive for business , so we can help you more correctly.
Best Regards,
Dedmon Dai
This is the Excel document that I am using to build my dashboard
Also here is a screenshot of the pbix while I try to input the column for utlization within the card.
Please let me know what exactly what else you need from the pbix file (I am sorry I am a bit new to the whole Power Bi experience and I was just taught the basics. The rest I am learning from other materials, this wonderful community and YouTube).
Thanks again
Hi Anonymous ,
Create the following measure and put it in the card visual:
Measure = CALCULATE(Your table name[ Utilisation for Chargeability Detail(Include Partners)],Your table name[ Operating Unit] = "Grand Total")Best Regards,
Dedmon Dai
First off, I want to thank you for taking this amount of time in helping me try to resolve this issue. I really appreciate it.
I have tried your suggestion and it gave me this result. What I would like it to be is a % total which would be 68% (utilization percentage exluding partners) and 66% (utilization percentage including partners).
With the measurement you provided, I recieved this:
Thanks again.
Hi Anonymous ,
I have edit the original reply ,please try again.
Hi Anonymous ,
Your table name means actual table name in your data model.
Measure = CALCULATE('Chargeability Detail by OU'[ Utilisation for Chargeability Detail(Include Partners)],'Chargeability Detail by OU'[ Operating Unit] = "Grand Total")Best Regards,
Dedmon Dai
Hello. I have revised the equation as advised, but is now giving me a different error:
I am going to include this for references perhaps I am missing something too.
This is the Excel file that I am working on. Its one of many tabs labaled, "Chargeability Details by OU"
And this is my file in Power Bi
Again, I am so sorry that this is taking a long time. I am still new at this and really enjoy learning from all of you. Thank you for all of your help. I am sorry if this request is bothering some of you. I deeply apologize.
Thank you again for helping. You are going above and beyond to help me learn. I deeply appreciate it.
Hi Anonymous ,
Use the following measure:
Measure = CALCULATE(MAX('Chargeability Detail by OU'[ Utilisation for Chargeability Detail(Include Partners)]),'Chargeability Detail by OU'[ Operating Unit] = "Grand Total")Best Regards,
Dedmon Dai
