Measure to calculate hours & $ of distinct value in a column

[reachkrs]

Hi

I am new to this forum & also to DAX. I have a Power BI model with data in columns A, B, C & D. I want to calculate the sum using a measure to sum as it appears in columns E & F. These are sums of columns C & D based on column A. Column A can repeat but I want to calculate it only once. Also, I want to calculate the Average & Median of these calculated measures. How to get this using DAX.

Seeking help from the community on how to address this and get the desired result.

[amitchandak · Accepted solution]

@reachkrs ,

Try measures like

Sumx(summarize(Table, Table[Project], Table[Hour]), [Hour])

Sumx(summarize(Table, Table[Project], Table[$]), [$])

[v-cazheng-msft · Accepted solution]

Hi @reachkrs ,

Not quite sure about the calculated logic of Sum Measure Result Hours. But you may try the following Measures.

Result $ Measure =
VAR minLine =
    CALCULATE (
        MIN ( 'Table'[Line] ),
        ALLEXCEPT ( 'Table', 'Table'[Project], 'Table'[Hour] )
    )
VAR NotRepeat =
    IF ( MAX ( 'Table'[Line] ) = minLine, MAX ( 'Table'[$] ), 0 )
RETURN
    NotRepeat

Sum Measure Result $ = SUMX('Table',[Result $ Measure])

Average Sum $ = AVERAGEX('Table',[Sum Measure Result $])

Median Sum $ = MEDIANX('Table',[Sum Measure Result $])

The result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft · Accepted solution]

Hi @reachkrs ,

Not quite sure about the calculated logic of Sum Measure Result Hours. But you may try the following Measures.

Result $ Measure =
VAR minLine =
    CALCULATE (
        MIN ( 'Table'[Line] ),
        ALLEXCEPT ( 'Table', 'Table'[Project], 'Table'[Hour] )
    )
VAR NotRepeat =
    IF ( MAX ( 'Table'[Line] ) = minLine, MAX ( 'Table'[$] ), 0 )
RETURN
    NotRepeat

Sum Measure Result $ = SUMX('Table',[Result $ Measure])

Average Sum $ = AVERAGEX('Table',[Sum Measure Result $])

Median Sum $ = MEDIANX('Table',[Sum Measure Result $])

The result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[reachkrs · Accepted solution]

Hi

Thank you.  The code provided by you gives exact result.

This solves my problem.

[reachkrs]

Thank you Amit.  Sumx works perfectly. 

Is it possible to calculate average & median of these values ?.  Since these are Measures Median, Average functions do not accept these as parameters.

[amitchandak · Accepted solution]

@reachkrs ,

Try measures like

Sumx(summarize(Table, Table[Project], Table[Hour]), [Hour])

Sumx(summarize(Table, Table[Project], Table[$]), [$])