Forum Discussion

SMG's avatar
SMG
Frequent Visitor
2 years ago
Solved

Aggregating values in one column grouped by a specific values in another column

I have a set of shipment charges by shipment that I need to aggregate to get the shipment's total revenue or by revenue component

 

Here is an example:

Shipment_NbrRevenue TypeRevenueWeight
1602530Fuel90122.3
1602530Misc82122.3
1602530Documents68122.3
1602530Handling66122.3
1602530Freight89122.3
1997544Fuel9887.6
1997544Misc5687.6
1997544Documents8587.6
1997544Handling9487.6
1997544Freight6787.6
1226866Fuel82754.3
1226866Misc87754.3
1226866Documents76754.3
1226866Handling90754.3
1226866Freight74754.3

 

In the table, the Shipment Number is the unique identifier. Each shipment has multiple types of charges and I want to show dynamically, what each shipment per weight breakdown is by all or some of the charge types.

 

The kicker is that the weights are duplicated by shipment. 

Here is how Ideally I could show a visual of the data:

All charge types selected:

Shipment_NbrTotal RevenueWeightRevenue by Weight
1602530 $395.00122.3 $3.23
1997544 $400.0087.6 $4.57
1226866 $409.00754.3 $0.54


However, if in the slicer, I select only Freight, I should see this:

Shipment_NbrTotal RevenueWeightRevenue by Weight
1602530 $89.00122.3 $3.23
1997544 $67.0087.6 $4.57
1226866 $74.00754.3 $0.54




 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    SMG 

    Here are the three measures:

    Total Revenue = SUM( Table16[Revenue] )
    S_Weight = MAX( Table16[Weight] )
    Revenue by Weigh = 
DIVIDE( 
    CALCULATE(
        [Total Revenue],
        REMOVEFILTERS( Table16[Revenue Type] )
    ),    
    CALCULATE([S_Weight])
)

     



2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    SMG 

    Here are the three measures:

    Total Revenue = SUM( Table16[Revenue] )
    S_Weight = MAX( Table16[Weight] )
    Revenue by Weigh = 
DIVIDE( 
    CALCULATE(
        [Total Revenue],
        REMOVEFILTERS( Table16[Revenue Type] )
    ),    
    CALCULATE([S_Weight])
)