Forum Discussion
Calculating percentages using multiple columns
- 6 years ago
Anonymous
I think I was able to get something close to what you are lookin for. The categorization of users and user count by categorization will both follow the slicer selections.
Categorization = VAR _Y = CALCULATE( SUMX( DISTINCT('Table'[user id]),1) ,'Table'[includes attachment?] = "Y" ) VAR _N = CALCULATE( SUMX( DISTINCT('Table'[user id]),1) ,'Table'[includes attachment?] = "N" ) RETURN SWITCH( TRUE(), _Y = 1 && _N = 0, "Always", _Y = 1 && _N = 1, "Sometimes", _Y = 0 && _N = 1, "Never" )User Count = CALCULATE( DISTINCTCOUNT('Table'[user id]), FILTER( VALUES('Table'[user id]), COUNTROWS( FILTER( Categories, [Attach Yes] = Categories[Y] && [Attach No] = Categories[N]) ) > 0 ) )I did add a categories table for use when grouping the user counts.
I have attached my sample file for you to look at.
Anonymous
I think I was able to get something close to what you are lookin for. The categorization of users and user count by categorization will both follow the slicer selections.
Categorization =
VAR _Y =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "Y"
)
VAR _N =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "N"
)
RETURN
SWITCH(
TRUE(),
_Y = 1 && _N = 0, "Always",
_Y = 1 && _N = 1, "Sometimes",
_Y = 0 && _N = 1, "Never"
)User Count =
CALCULATE(
DISTINCTCOUNT('Table'[user id]),
FILTER(
VALUES('Table'[user id]),
COUNTROWS(
FILTER(
Categories, [Attach Yes] = Categories[Y] && [Attach No] = Categories[N])
) > 0
)
)
I did add a categories table for use when grouping the user counts.
I have attached my sample file for you to look at.
jdbuchanan71 this is perfect, thanks very much!
Just what I needed and learnt a lot from the way you've laid out the various components. Really appreciate it.