Forum Discussion

cheid_4838's avatar
cheid_4838
Icon for Helper IV rankHelper IV
1 year ago
Solved

creating measure based on week start date

I thought this would be pretty straight forward, but I need help creating a measure that calculates the number of truck drivers that have less than 200 miles per ship day and 1,500 miles per week.  I want to then take this number and divide it by the total number of drivers.  I have tried using ALL, AllSelected, Allexcept, and filter, but nothing seems to be working.  Any helpt is greatly appreciated.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  cheid_4838, hello danextian , thanks for your concern about this case.

     

    The issue with ALLEXCEPT is its reliance on the context provided by the visual.

     

    When you remove key columns, it behaves differently, often leading to incorrect results.

     

    Using SUMX with VALUES ensures that the calculation explicitly considers each driver, making it robust to changes in the visual context.

     

    Drivers < 1500（2） = 
SUMX(
    VALUES(DOMTAR[Driver]),
    VAR Miles =
        CALCULATE(
            SUM(DOMTAR[Total Miles]),
            ALLEXCEPT(DOMTAR, DOMTAR[Driver], CalendarLookUp[Week Start Date])
        )
    RETURN IF(Miles < 1500, 1, 0)
)

     Result:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi cheid_4838, thank you for your feedback.

     

    If possible, please upload sample pbix file for better troubleshooting.

    Thank you for your understanding.

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi cheid_4838 

     

    Please post a workable sample data (not an image), your expected result from that sample data (for example, total driver for this week start = ?? vs total drivers according to the miles) and the reasoning behind.

    • cheid_4838's avatar
      cheid_4838
      Icon for Helper IV rankHelper IV
      1 year ago

      I am not able to upload a file.  Below is the expected result and what needs to be calculated.

      Row LabelsTotal DriversSum of Driver Count
      >200 Miles Per Day      		Sum of Driver Count
      <1500 Miles Per Week      		% Drivers
      >200 Miles Per Day      		% Drivers
      >1500 Miles Per Day
      12/8/2024105131912.4%18.1%
      12/15/202492132114.1%22.8%
      • cheid_4838's avatar
        cheid_4838
        Icon for Helper IV rankHelper IV
        1 year ago

        This is the latest logic I used. When I use this I get the results I want in the column as shown with the "1" being shown for any miles less than 1,500. The problem is that it won't sum when I remove miles and driver from this table. It just shows 0.  How do I get it to total the correct amount of 5?

         

        Drivers < 1500 =
        VAR Miles = CALCULATE(SUM(DOMTAR[Total Miles]),ALLEXCEPT(CalendarLookUp,CalendarLookUp[Week Start Date]))
        VAR DriverCount = IF(Miles < 1500,[Drivers (Distinct)],0)

        RETURN DriverCount