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...
  • jdbuchanan71's avatar
    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.