Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 %?
 

 

Need Help

4 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hello Anonymous , 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!!
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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. 

       

       

      • Kishore_KVN's avatar
        Kishore_KVN
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Hello Anonymous , 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. 

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