Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

 

2020-07-28_20-00-53.png

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User
Super User

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

 

2020-07-28_20-00-53.png

Anonymous
Not applicable

@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
Super User
Super User

@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
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.