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
bdue
Helper I
Helper I

Divide row by column total while keeping date filter

Hi,

 

I am trying to generate a column total that I can use in measures to divide each row by for a % of total.  I see the quick measures for "total for category" with and without filters applied, but neither gets me what I need.

 

In the example below, I have a table with many data dates and rows returning employee FTEs (full time equivalents). 

 

FTEs total all =
CALCULATE(sum('Dayforce'[FTEs]), all('Dayforce'))

 

FTEs total allselected =
CALCULATE(SUM('Dayforce'[FTEs]), ALLSELECTED('Dayforce'[DataDate]))
 
The first returns the total across all data dates (not what I need), the second tries to filter to the selected data date, but it generates a sum of the FTEs for just that location, when I want the total (4078) instead.  How can I get each row to return the 4078 value so I can ultimately use that same code to divide the location's FTE sum by the total (4078) for a %?
 
BI Table visual.png

 

4 REPLIES 4
Kishore_KVN
Super User
Super User

Hello @bdue , your requirement is not clear but still if you create All and ALLSELECTED for the table in a measure instead of a column you will get your required output. 

 

Instead of 

FTEs total allselected =
CALCULATE(SUM('Dayforce'[FTEs]), ALLSELECTED('Dayforce'[DataDate]))
use
FTEs total allselected =
CALCULATE(SUM('Dayforce'[FTEs]), ALLSELECTED('Dayforce'))
 
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Thank you @Kishore_KVN , but I get the same result when I make that change.  It just returns the filtered value of FTEs for the location, not the column total.  What I am trying to get is the 4078 figure in each row. 

 

bdue_0-1687190938336.png

 

Hello @bdue , using all and all selected will give the total by remove all the external filters on that calculation. Can you please refer below screenshot for your reference. 

Kishore_KVN_0-1687192025822.png

If possible please share sample pbix will try to provide maximum help. 

 

Thanks for staying with me here!  I can't send my original PBIX file, but I wonder if my issue is that I have multiple connnected datasets and this table is referencing a location column from another table that is linked by relationship to the employee table. 

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