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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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