Percentage of Grand Total

[Anonymous]

I have a visual that looks as follows:

The total terms for this dataset is 7141 (Count of ID's)

The Terms % shown in the above visual is wrong. I want the Tems % = Terms by termination reason / Total Terms.

For example under voluntary we have so many reasons and a Terms # for it. Example, Left to work outise industry = 1324. so i need my Terms % column to show 1324 divided by 7141 =19%. But it shows 22% in this case. I am not sure how exactly to write the DIVIDE DAX as this is a matrix.

Basically Terms % = Terms column in matrix / 7141, where 7141 = COUNT(Table[ID])

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could get it just use ALL Function in the formula as below:

Terms % =
DIVIDE (
    CALCULATE ( COUNTA ( Table1[ID] ) ),
    CALCULATE ( COUNTA ( Table1[ID] ), ALL ( Table1 ) )
)

Result:

Best Regards,

Lin

[Anonymous]

Hi @Anonymous ,

Is 7141 a Fixed value?

How do you get that value?

Thank,s

Tejaswi

[Anonymous]

@Anonymous  Hey 7141 is not a fixed value. It is the COUNT OF ID's (Or the total terms at the moment)

So at the moment for Left Outside industry should be 1324/7141 = 19%. But I have 22%. 

[Anonymous]

Hi @Anonymous ,

So when you say Total terms do you mean:

Total terms of Voluntary(4007)+ Total terms of Involuntary (2096)??

If yes, than the total of these is 6103.  

and when you divide to get the % Terms it comes out to be the correct the value (1324/6103)= 22%

Let me know if I am egtting your question correct.

Thanks,

Tejaswi

[Anonymous]

@Anonymous  Sorry about the confusion.

So the matrix i have is filtered to show top 15. Hence the 6103. But in actual at the moment i have in  total ( voluntary plus invol) of 7141 people. So though the matrix shoes 6103, I want the percentage to be a percentage of the TOTAL TERMS which is 7141. 

Where 7141 = COUNT(Table[ID]) and is the total terms of vol + invol.

[Anonymous]

Hi @Anonymous ,

Try these two measures.

I couldn't test this until I know your data, But you can give a shot with these measures.

Count of Terms = COUNT(table[terms])  ---------> these to give you the total Count of terms (vol+ Invol)

% Terms=divide(table [Count of Terms],CALCULATE(table [Count of Terms],ALL(Table[TERMS])))

Let me know if this works or else you can provide me a sample data.

Thank,s

Tejaswi

[Anonymous]

This is the link with the dataset:

https://drive.google.com/open?id=1AcLYt2zQZx5ymg_O8Cuj_13roZZj3_BS

ID - the unique ID of the employee (If u see the count it will be 7141 - TOTAL COUNT)

Termination Reason - can be voluntary / invol / N / blank -  For the matrix purpose i am using only vol and invlo

Term Event Reason - the description of the reason categorized by Termination Reason. I have chosen top 15 reasons for Term Event Reason by count of ID for the matrix. (This would give the 6103)

Thren my matrix would look as follows:

[Anonymous]

Hi @Anonymous ,

You just need to Calculate the % of grand total.

No need of any measure.

My output:

Let me know if this works:

Thanks,

Tejaswi