Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have table, with columns for Account Code, Organization, Location, Category, Year, and Payroll Sum. I would like to create a % of Total of Payroll, by Location filtering on year, category, and organization. I tried doing this a multitude of ways but nothing is working! How do I execute this properly in Power BI?
Solved! Go to Solution.
Try
divide(
calculate(sum(table[payroll Sum])),
calculate(sum(table[payroll Sum]),ALLEXCEPT(table,table[category],table[year]))
)
Hi @jaynesa ,
Please try the DAX below
Measure =
VAR payroll =
MAX ( 'Table (2)'[Payroll Sum] )
VAR sum_condition =
CALCULATE (
SUM ( 'Table (2)'[Payroll Sum] ),
ALLEXCEPT (
'Table (2)',
'Table (2)'[Category],
'Table (2)'[Organization],
'Table (2)'[Year]
)
)
RETURN
DIVIDE ( payroll, sum_condition )
Here is the sample pbix.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Would this work if there were more columns in your table that were'nt used for the measure? I'm confused why this isn't working for my table.
Hi @jaynesa ,
If the problem persists,could you please share sample pbix?(Please mask any sensitive data before uploading)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Why can't I find a way to upload the .pbix file? There doesn't seem to be an option to.
Hi @jaynesa ,
You could share the pbix link with OneDrive for business.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Liang, attached is a copy of the .pbix file. Please take a look, thanks!
Try
divide(
calculate(sum(table[payroll Sum])),
calculate(sum(table[payroll Sum]),ALLEXCEPT(table,table[category],table[year]))
)
This worked!!!
Hey @jaynesa ,
I assume this measure will provide what you are looking for:
Payroll percentage=
var PayrollLoc =
ADDCOLUMNS(
SUMMARIZE(
'<tablename>'
, '<tablename>'[Account]
, '<tablename>'[Location]
, '<tablename>'[Category]
, '<tablename>'[Year]
)
, "_loc" , CALCULATE(SUM('<tablename>'[Payroll Sum]))
)
var PayrollALL =
ADDCOLUMNS(
SUMMARIZE(
'<tablename>'
, '<tablename>'[Account]
, '<tablename>'[Category]
, '<tablename>'[Year]
)
, "_All" , CALCULATE(SUM('<tablename>'[Payroll Sum]), ALL('<tablename>'[Location]))
)
return
DIVIDE(PayrollLoc , PayrollALL)
Just replace <tablename> with the name of your table, include <> in the replacement.
Regards,
Tom
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
102 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |