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:

 

dateuser idrecord idrecord typeincludes attachment?
01/01/20A100XY
01/01/20B101YN
02/01/20A101XY
02/01/20B103XY
02/01/20C104YN
03/01/20A105ZN
03/01/20B106XN
03/01/20C107YN

 

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.

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.

     

4 Replies

  • 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

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

      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.