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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Brian_Hunt
Frequent Visitor

Measure calculation for subtotal based on sub-hierarchy grouping on condition

I  have a column of data that counts orders by employee, and another that counts if the order has been completed. I created a measure that calculates the completion rate and dropped all this in a matrix which accurately calculates for each level in the org hierarchy (employee, leader, 2nd level, department, etc). Data is dynamic based on the selected dates in the slicer.

 

I want another measure that calculates the % of teams that are at or above a specific completion rate goal in the same matrix. At the employee level it would just be blank, but the next level up would calculate based on each individual employee >= goal / total employees in that grouping. How do you get the DAX to calculate the condition for each item within the current grouping?

 

PB Desktop January 2022 is the current version I'm allowed to work in. Below is a simplistic illustration of what I'm trying to accomplish (but I'd be doing that for 6 levels of hierarchy). In excel I would probably use SUMPRODUCT, but in DAX/PBI to count the number of teams it might be a combination of ISINSCOPE and some sort of count unique, then do the same thing adding on the conditional calculation.

 

ORG LEVELITEMSCOMPLETEDComplete rate (comp/items)teams at >= 50%%of teams >= 50%
Department381642%3=3 / 5 employees in dept
Leader 1A13753%2=2 / 2 employees in team
Emp 11A10550%group row1 
Emp 21A3266%group row1 
Leader 2A5120%0=0 / 1 employee in team
Emp 12A5120%group row2 
Leader 3A20840%1=1 / 2 employees in team
Emp 13A15533%group row3 
Emp 23A5360%group row3 
      

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Brian_Hunt ;

You could create measures as follow:

 

teams at >50% = CALCULATE(COUNT('Table'[Level3]),FILTER('Table',[Complete rate]>=0.5))
of teams >50% = 
IF(ISINSCOPE('Table'[Level3]),BLANK(),
[teams at >50%]/COUNT('Table'[Level3]))

 

The final show:

AilsaTao_6-1660721640278.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @Brian_Hunt ;

You could create measures as follow:

 

teams at >50% = CALCULATE(COUNT('Table'[Level3]),FILTER('Table',[Complete rate]>=0.5))
of teams >50% = 
IF(ISINSCOPE('Table'[Level3]),BLANK(),
[teams at >50%]/COUNT('Table'[Level3]))

 

The final show:

AilsaTao_6-1660721640278.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HoangHugo
Solution Specialist
Solution Specialist

Hi

I think

measure

       %team =
           var   count_employee = DISTINCTCOUNT (employee)
           var   count_employee>50% =   CALCULATE ( COUNTROW(SUMMARIZE (date,employee)), COMPLETE rate >=50%)
           var  group/department = SELECTEDVALUE (employee)

            return

             if ( group/department <> blank (), blank (), count_employee>50% / count_employee)

 

This is a huge step towards what I'm trying to accomplish. I was getting a DAX error " A function 'PLACEHOLDER' has been used..." and had to switch the 2nd var to add FILTER to the CALCULATE in order to use the aformentioned measure:

CALCULATE ( COUNTROWS(SUMMARIZE (date,employee)),FILTER(table1,CompleteRate >= 50%)

Unfortunately the percentages were not what I was expecting. I then used SUMMARIZE to create a table to see what it was actually doing, and broke otu the distinct count, and row count >50% to compare data. I managed to the get whole number counts of distinct employee and employee > 50 to add up, but the [>50] / [distinct count] was Summing the individual row percentages rather than recalculating at the hierarchy level above. I'll play around with summarize some more to find the disconnect.

Dear,

Can you share your file to see detail

Unfortunately my organization doesn't allow the uploading of any contents to this site, despite being connected to login via windows authentication. I can't even upload a picture of the layout or structure which is why I had to create the ugly comment table.

 

The info you've given me so far has leaped me several steps forward in just being able to summarize and calculate off an already calculated measure.

Thank you. I'll give this a try and let you know.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors