How to calculate a % of total based on specific Filters

[jaynesa]

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?   

[V-lianl-msft]

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.

[jaynesa]

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.

[V-lianl-msft]

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.

[jaynesa]

Why can't I find a way to upload the .pbix file?  There doesn't seem to be an option to.

[V-lianl-msft]

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.

[jaynesa]

https://mydrive.merck.com/:u:/r/personal/jaynesa_merck_com/Documents/Payroll%20Example.pbix?csf=1&e=... 

[jaynesa]

Hi Liang, attached is a copy of the .pbix file.  Please take a look, thanks!

[amitchandak · Accepted solution]

@jaynesa 

Try

divide(
calculate(sum(table[payroll Sum])),
calculate(sum(table[payroll Sum]),ALLEXCEPT(table,table[category],table[year]))
)

[jaynesa]

This worked!!!

[TomMartens]

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