Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
jaynesa
Helper II
Helper II

How to calculate a % of total based on specific Filters

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?   

 

powebi2.jpgpowerbi.jpg

 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jaynesa 

Try

 

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

 

View solution in original post

10 REPLIES 10
V-lianl-msft
Community Support
Community Support

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 )

test_%of total.PNG

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!

amitchandak
Super User
Super User

@jaynesa 

Try

 

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

 

This worked!!!

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors