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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Percentage of Grand Total

I have a visual that looks as follows:

Terms.png

 

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])

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

3.JPG

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

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:

3.JPG

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @Anonymous ,

 

Is 7141 a Fixed value?

How do you get that value?

 

Thank,s

Tejaswi

Anonymous
Not applicable

@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
Not applicable

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
Not applicable

@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
Not applicable

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
Not applicable

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:

Terms.png

Anonymous
Not applicable

Hi @Anonymous ,

 

You just need to Calculate the % of grand total.

No need of any measure.

My output:

Capture12.PNG

Let me know if this works:

Thanks,

Tejaswi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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