Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
Solved! Go to 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
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
Hi @Anonymous ,
Is 7141 a Fixed value?
How do you get that value?
Thank,s
Tejaswi
@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%.
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 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.
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
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:
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |