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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Bhaskar123
Frequent Visitor

Need to group category values

 

Bhaskar123_0-1673003413348.png

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 ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vbinbinyumsft_0-1673247583700.png

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

vbinbinyumsft_0-1673247583700.png

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
Super User
Super User

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

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_0-1673005013044.png

 

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

Bhaskar123_0-1673013917389.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.