Forum Discussion
Count with Values in a column
Hi team,
I have a table with columns Date,Counts, Type, Category
Type Column has 6 types: A,B,C,D,E,F
Date column consists of 2022 year dates
Counts column has number of counts w.r.t type
Category column has Internal and External
Type A,B,C,D falls under Internal category
Type E,F falls under External Category
Requirement:
I want to show the percentage in such a way that, when i select particular month it should calculate: Sum of internal counts/(Sum of internal counts+Sum of external counts).
This percentage value i need to show in card visual and it should filter as per date selection.
please help me out with the solution.
Hi ghouse_peer ,
Please adding these measures :-
Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )Sum External = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "External") )
Percentage =DIVIDE([ Sum Internal], [Sum Internal]+[Sum External])
You can also confirm these different measure into a single var function :-
Percentage = VAR Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )
VAR Sum External = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "External") )return DIVIDE(Sum Internal, Sum Internal+Sum External)
Percentage = VAR Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )
VAR Sum All = CALCULATE( SUM('Table'[Count] ), ALL('Table')return DIVIDE(Sum Internal, Sum All)
Hope this was helpful forThanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
2 Replies
Hi ghouse_peer ,
Please adding these measures :-
Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )Sum External = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "External") )
Percentage =DIVIDE([ Sum Internal], [Sum Internal]+[Sum External])
You can also confirm these different measure into a single var function :-
Percentage = VAR Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )
VAR Sum External = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "External") )return DIVIDE(Sum Internal, Sum Internal+Sum External)
Percentage = VAR Sum Internal = CALCULATE( SUM('Table'[Count] ), FILTER('Table', 'Table'[Category] = "Internal") )
VAR Sum All = CALCULATE( SUM('Table'[Count] ), ALL('Table')return DIVIDE(Sum Internal, Sum All)
Hope this was helpful forThanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos Chi ghouse_peer
try like:
Pct =VAR _int =CALCULATE(SUM(TableName[Counts]),TableName[Category]="Internal")VAR _allcategory =CALCULATE(SUM(TableName[Counts]),ALL(TableName[Category]))RETURNDIVIDE(_int, _allcategory)
