Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI Aggregation Tables and Distinct Count a value

Hi, I am currently setting up my first data model using Power BI Aggregation tables and am having a problem getting distinct count to work with it.  I seem to be able to get sums to work just fine b...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Based on your description, you can create this calculated table:

    New Table =
SUMMARIZE (
    'Table',
    'Table'[StopNumber],
    'Table'[TripNumber],
    'Table'[Billdate],
    'Table'[OrderLocationCode],
    'Table'[Location],
    "Total_Stop",
        CALCULATE (
            SUM ( 'Table'[StopActualMiles] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Miles",
        CALCULATE (
            SUM ( 'Table'[Weight1] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Drive",
        CALCULATE (
            SUM ( 'Table'[DriveTimeHours] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        )
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    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
Not applicable
5 years ago

Hi @v-yingjl Mows @amitchandak

Please find the data below

I want to count different stops and trips, but add up the other values

StopNumberTripNumberBilldateOrderLocationCodeStopActualMilesWeight1DriveTimeHours
496889887789011888610/3/202055555007.1666
496889897789011888610/3/20205555511295716.9166
496889907789011888610/3/20205555518158356.6666
496889917789011888610/3/2020555559207.75
496889927789011888710/3/202055555009.5333
496889937789011888710/3/2020555552885348.8333
496889947789011888710/3/2020555551358258.9666
496889957789011888710/3/2020555559129229.5333
496889967789011888710/3/2020555552751699.3333
496889977789011888710/3/20205555568010.1
496889987789011888810/3/202055555007.5
496889997789011888810/3/2020555558057116.45
496890007789011888810/3/20205555510667547.0833
496890017789011888810/3/2020555551707.5
496890027789011888910/3/202055555005.7666
496890037789011888910/3/2020555556793235.0666
496890047789011888910/3/2020555552806.2666
496890057789011889010/3/2020555550012.8
496890067789011889010/3/202055555111327712.2
496890077789011889010/3/20205555523700312.3666
496890087789011889010/3/20205555575541812.1666
496890097789011889010/3/202055555194012.9166
496890107789011889110/3/202055555008.55
496890117789011889110/3/2020555558357497.3833
496890127789011889110/3/202055555265467.55
496890137789011889110/3/202055555386358.65
496890147789011889110/3/2020555558909.55

OrderLocationCodeLocation
55555Apple Sales

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

Based on your description, you can create this calculated table:

New Table =
SUMMARIZE (
    'Table',
    'Table'[StopNumber],
    'Table'[TripNumber],
    'Table'[Billdate],
    'Table'[OrderLocationCode],
    'Table'[Location],
    "Total_Stop",
        CALCULATE (
            SUM ( 'Table'[StopActualMiles] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Miles",
        CALCULATE (
            SUM ( 'Table'[Weight1] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Drive",
        CALCULATE (
            SUM ( 'Table'[DriveTimeHours] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        )
)

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.