Creating new measure column groupby existing column in table

[Anonymous]

Hi,

 I have to acomplish a requirement where I need to calculate transport cost per KM travelled (Transport cost/KM travelled) group by month and country. As per the data I have 3 rows for each month where as Transport cost value is in transport cost colmn  Row 1 and KM travelled is the summation of row2 and row 3 from KM travelled column. Is there any way to achive this?

month      Country        Transport Cost           KM travelled       transport cost per KM

 Jun            USA                 A                               NULL                         A/(B+C)

 Jun            USA               NULL                             B                       

 Jun            USA               NULL                             C

Regards,

Rakesh

Regards,

Rakesh

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

As the sample data and expect result you shared, we can create a column using following DAX:

transort cost per KM =
VAR m = [month]
VAR c = [Country]
VAR t =
    FILTER ( 'Table', AND ( 'Table'[month] = m, 'Table'[Country] = c ) )
VAR cost =
    SUMX ( t, [Transprot Cost] )
VAR travelled =
    SUMX ( t, [KM travelled] )
RETURN
    IF (
        AND ( [Transprot Cost] <> 0, NOT ISBLANK ( [Transprot Cost] ) ),
        cost / travelled,
        BLANK ()
    )

But if you want a measure using in visual, we can create this measure using similar logic.

measure = 
VAR m =
    SELECTEDVALUE ( 'Table'[month] )
VAR c =
    SELECTEDVALUE ( 'Table'[Country] )
VAR t =
    FILTER ( ALL ( 'Table' ), AND ( 'Table'[month] = m, 'Table'[Country] = c ) )
VAR cost =
    SUMX ( t, [Transprot Cost] )
VAR travelled =
    SUMX ( t, [KM travelled] )
RETURN
cost / travelled

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Thank you so much for the solution very informative.