Calculating percentages using multiple columns

[Anonymous]

Hi, 

I have some data similar to the below:

date	user id	record id	record type	includes attachment?
01/01/20	A	100	X	Y
01/01/20	B	101	Y	N
02/01/20	A	101	X	Y
02/01/20	B	103	X	Y
02/01/20	C	104	Y	N
03/01/20	A	105	Z	N
03/01/20	B	106	X	N
03/01/20	C	107	Y	N

I just want to calculate the percentage of users that always, never or sometimes included an attachment with their records.

The part giving me a headache is was hoping to calculate this dynamically with or without a specific record type selected and also for a filtered date range. Eg. the categorisation (sometimes, always, never) should be recalculated if filtering by a specific record type for a specific date range.

I had started by, in powerquery, grouping by user id and record type and then pivoting on 'record includes attachment'. While this lets me write a simple cound distinct user ids measure it is only accurate when a record type is selected and won't allow filtering by date. 

I think I want to write a measure(s) that:

• For a user id returns all records and the corresponding 'Y' or 'N' value for an attachment
• Categorises each user based on those values (ie. sometimes, always, never)
• Recalculates the category based on specific record types
• Recalculates the category based on specific date range
• Counts the distinct user ids in each category to calculate the percentage of users for the filtered data

Can anyone suggest an approach to tackling this problem please? Any guidance much appreciated.

[jdbuchanan71 · Accepted solution]

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

@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.

[amitchandak]

@Anonymous , You can create a new column in the table like

category =
var _A = countx(filter(table,table[user id] = earlier([user id])),[user Id])
var _Y = countx(filter(table,table[user id] = earlier([user id]) && Table[includes attachment] ="Y"),[user Id])
var _N = countx(filter(table,table[user id] = earlier([user id]) && Table[includes attachment] ="N"),[user Id])
return
switch ( True(),
[_A] = [_Y] ,"Always",
[_A] = [_N] , "Never",
"Sometime")

You can also have measure like these

always =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="Y")),[_1]=[_2]),[User id])

Never =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="N")),[_1]=[_2]),[User id])

sometime =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="Y")),not(isblank([_2])) && [_1]>[_2]),[User id])

[Anonymous]

@amitchandak 

Thanks very much for this, much appreciated. Definitely need to get my head around using earlier and also using switch. I was getting some slightly erroneous results when filtering on the record type but probably something on my end.

Thanks again