Forum Discussion

Brian_Hunt's avatar
Brian_Hunt
Frequent Visitor
4 years ago
Solved

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 mat...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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:


    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's avatar
HoangHugo
Solution Specialist
4 years ago

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)

 

  • Brian_Hunt's avatar
    Brian_Hunt
    Frequent Visitor
    4 years ago

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

  • Brian_Hunt's avatar
    Brian_Hunt
    Frequent Visitor
    4 years ago

    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.

    • HoangHugo's avatar
      HoangHugo
      Solution Specialist
      4 years ago

      Dear,

      Can you share your file to see detail

      • Brian_Hunt's avatar
        Brian_Hunt
        Frequent Visitor
        4 years ago

        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.