Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need to group category values

 

 

Hi Team, 
here i have column like Shipcity and duration and Geostop Count 
for same driverkey i am getting two rows with same city name 
for 170402 city HALIFIX  duration 8.55 and 8.95 and count 12 and 8 

i want to get only single city name like HALIFIX with sum of geostop count = (12+8 = 20)  and duration = (8.55 +8.95= 17.5)

How to sum two rows for same driver key or city ?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please try below steps:

    1. create two measure with below dax formula

    Measure =
VAR cur_dk =
    SELECTEDVALUE ( 'Table'[DriverKey] )
VAR cur_city =
    SELECTEDVALUE ( 'Table'[ShipCity] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[DriverKey] = cur_dk
            && 'Table'[ShipCity] = cur_city
    )
RETURN
    SUMX ( tmp, [Geo Stop Count] )

    Measure2 =
VAR cur_dk =
    SELECTEDVALUE ( 'Table'[DriverKey] )
VAR cur_city =
    SELECTEDVALUE ( 'Table'[ShipCity] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[DriverKey] = cur_dk
            && 'Table'[ShipCity] = cur_city
    )
RETURN
    SUMX ( tmp, [Duration] )

    2. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago
    Anonymous 
    Total Duration =     CALCULATE(SUM('Test data'[Duration]),ALLEXCEPT('Test data','Test data'[Shipcity]))
    Total geostop count = CALCULATE(SUM('Test data'[geostop count]),ALLEXCEPT('Test data','Test data'[Shipcity]))

    *If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Mahesh,
      Thanks for replay,

      here GeoStop count is Measure  so we are unable to sum the measured values.
      i have tried this approch but no luck

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Mahesh0016 
        Thanks for replay,

        here GeoStop count is Measure  so we are unable to sum the measured values.
        i have tried this approch but no luck

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please try below steps:

    1. create two measure with below dax formula

    Measure =
VAR cur_dk =
    SELECTEDVALUE ( 'Table'[DriverKey] )
VAR cur_city =
    SELECTEDVALUE ( 'Table'[ShipCity] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[DriverKey] = cur_dk
            && 'Table'[ShipCity] = cur_city
    )
RETURN
    SUMX ( tmp, [Geo Stop Count] )

    Measure2 =
VAR cur_dk =
    SELECTEDVALUE ( 'Table'[DriverKey] )
VAR cur_city =
    SELECTEDVALUE ( 'Table'[ShipCity] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[DriverKey] = cur_dk
            && 'Table'[ShipCity] = cur_city
    )
RETURN
    SUMX ( tmp, [Duration] )

    2. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.