Need to group category values

[Bhaskar123]

 

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 ?

[Anonymous · Accepted solution]

Hi @Bhaskar123 ,

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.

[Mahesh0016]

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

[Bhaskar123]

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

[Bhaskar123]

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