Create an Average row in matrix.

[Prasanthh60]

Hi All,

Thanks in advance. The following is where I am at when I try to design a custom dashboard.

I used data from three sources to build a matrix table. Under the total row, I also require an average row, so that I may see the daily average. The July month data will be in day-wise format if I drilldown.

[Prasanthh60]

Thanks for responding. Actually, i need avg influx, avg production, avg target per day. The July month data is Day wise data. If i drill down, I can see all the daywise data. 

Hence, I need an per day avg of the Month. 

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result: = 
VAR _sum =
    SUM ( data_fact[value] )
VAR _total =
    CALCULATE ( SUM ( data_fact[value] ), ALL ( matrix_dim ) )
VAR _monthlyaverage =
    AVERAGEX (
        ALL ( matrix_dim[Year-Month sort number], matrix_dim[Year-Month] ),
        CALCULATE ( SUM ( data_fact[value] ) )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( matrix_dim[Year-Month] ),
        "Total", _total,
        "Average", _monthlyaverage,
        _sum
    )

[Prasanthh60]

Thnank you. I have not tried writing this complex formulas in POwer bi. I will try with the above.

[pborah]

Can I ask what the interpretation of the average in the context of a row would be? Like you want to know average Influx, Production, etc per day for July and perhaps each month? 

Because on a monthly roll up, if you have the total, average for July kinda loses meaning. But if you say for the whole year on average the Influx or Production was x units per month, that makes more sense in terms of valuable business information. 

You might benefit more from transposing your matrix where the months are in columns and your product heirarchy is in rows.