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

This may be a stupid question but I am struggling with it.  

How do I join a unaggregated table back to my aggregated ones?  Since if I create one with a distinct count or list of the numbers i'm counting the arrow points in the wrong direction.  From the distinct one back to the agg.  

 

Do I need to have them all connect to the same dims and have them filter that way?

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

Hi Anonymous ,

Based on your description, not certain what is your expected output. Could you please consider sharing a dummy sample for further discussion without any fact information?

 

Sample data and expected output would help tremendously.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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.