Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Friends,
Looking for help and hoping someone has already navigated this water and can help. I had a tabular visual in Excel which looks like below. My Data table has Bldg and New/Old attached to Patient ID and so I can aggregate them, and in my Excel table, just divide the cells like New/Total to create the % subtotals.
In Power BI, I have a Matrix visual where I have the Bldg and New/Old as rows, month as a column and Values as Count (patient ID) so I am able to create the rows for New, Old and Total. However, I am not able to create the % anymore as not sure how to divide the New/Total.
Do I need to create a measure or calculated column? How do I go about it? Thanks a lot for your help.
EXCEL VIEW:
Jan | Feb | |
Bldg 1 New | 430 | 402 |
Bldg 1 Old | 949 | 1,824 |
Bldg 1 Total (calculation) | 1379 | 2,226 |
Bldg 1 New (calculation) | 31% | 18% |
Bldg 1 Old % (calculation) | 69% | 82% |
Bldg 2 New | 100 | 400 |
Bldg 2 Old | 900 | 1600 |
Bldg 2 Total (calculation) | 1000 | 2000 |
Bldg2 New % (calculation) | 10% | 20% |
Bldg2 Old % (calculation) | 90% | 80% |
Solved! Go to Solution.
Hi @ss0102 ,
Column or Measure is al
Here's the formula for calculated column.
jan%_c = CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg]))
feb%_c = CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg]))
Best Regards,
Jay
Hi @ss0102 ,
Column or Measure is al
Here's the formula for calculated column.
jan%_c = CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg]))
feb%_c = CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg]))
Best Regards,
Jay
This is so helpful! Thank you
Hi @ss0102 ,
Please try the below measures:
JAN % = DIVIDE(CALCULATE(SUM('Table (2)'[JAN])),
CALCULATE(SUM('Table (2)'[JAN]),ALLEXCEPT('Table (2)','Table (2)'[BLDG])),0)
FEB % = DIVIDE(CALCULATE(SUM('Table (2)'[FEB])),
CALCULATE(SUM('Table (2)'[FEB]),ALLEXCEPT('Table (2)','Table (2)'[BLDG])),0)
Aiolos Zhao
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |