Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating percentages using multiple columns

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/0...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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