Average the number based one main column in DAX

[VijayAntonyM]

Team,

I Have 3 columns in table , Stations , Hour and Count as below,

Station  Hour     Count  Average

ST1        1 AM    10        13.3

ST1        2 AM    30        13.3

ST1        3 AM                13.3

Average should be calculated by Station only eventhough the Hour column does have blank, 40/3=13.33

Kindly help

regards

M. Vijay Antony

[v-nuoc-msft · Accepted solution]

Hi @VijayAntonyM 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. Based on station groupings, the average value is calculated.

Average = 
    VAR _COUNT = 
        CALCULATE(
            SUM('Table'[Count]), 
            FILTER(ALL('Table'),
                'Table'[Station] = MAX('Table'[Station])
                )
            )
    VAR _STATION = 
        CALCULATE(
            COUNTROWS('Table'), 
            FILTER(ALL('Table'), 
                'Table'[Station] = MAX('Table'[Station])
                )
            )
RETURN DIVIDE(_COUNT, _STATION)

Here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

C = SUM(Data[Count])

Measure = divide(CALCULATE([C],ALL(Data[Hour])),CALCULATE(COUNTROWS(Data),ALL(Data[Hour])))

Hope this helps.

[v-nuoc-msft · Accepted solution]

Hi @VijayAntonyM 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. Based on station groupings, the average value is calculated.

Average = 
    VAR _COUNT = 
        CALCULATE(
            SUM('Table'[Count]), 
            FILTER(ALL('Table'),
                'Table'[Station] = MAX('Table'[Station])
                )
            )
    VAR _STATION = 
        CALCULATE(
            COUNTROWS('Table'), 
            FILTER(ALL('Table'), 
                'Table'[Station] = MAX('Table'[Station])
                )
            )
RETURN DIVIDE(_COUNT, _STATION)

Here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayAntonyM]

Thanks for the solution

[VijayAntonyM]

Team, Can someone help me on this?

[Anonymous]

Hi @VijayAntonyM ,

Thanks for using Fabric Community.
Is this query related to DAX - Power BI?

[VijayAntonyM]

Yes , related to DAX in Power BI desktop.

Makesure the relationship, filters should not be affected

[Anonymous]

I am moving this to Power BI forum there you can get some help.

Thank you

[VijayAntonyM]

thanks much