Show "Average" in the "Total" column of a Table.

[tiez11]

Hello,

I've already searched for possible solutions in other topics, but all of those don't seem to work with the measure that I use. I want to show the average for each column (see screenshot). The percentages are the uptime for each device.

The measure that I use is:

Recalculated Availability = IF(ISBLANK(SUM(fact_data[Downtime BH])),1,DIVIDE(SUM(dim_calendar[WorkingHours])-SUM(fact_data[Downtime BH]),(SUM(dim_calendar[WorkingHours]))))

I hope you guys can help me!

Thanks in advance. 

(I can't share the PowerBi file.)

[jgeddes · Accepted solution]

You can try the measure

Recalculated Availability =

var _table =

SUMMARIZE(fact_data, fact_data[Serial No.], "_RA", IF(ISBLANK(SUM(fact_data[Downtime BH])),1,DIVIDE(SUM(dim_calendar[WorkingHours])-SUM(fact_data[Downtime BH]),(SUM(dim_calendar[WorkingHours])))))

Return

AVERAGEX(_table, [_RA])

[tiez11]

Thanks for your answer, the calculation is now done the right way but almost all of my serial numbers dissapear and I have blanks?

[jgeddes]

Do you have serial no information in your fact_data table? If so try that field in the measure instead of the assets table.

[tiez11]

There is serial information in the fact_Data table, but "all" the serials are in a dim table (Assets), from that table I select the "Serial No." field. This so, because my fact data contains ticketdata and if for some serial number there is no "ticket", it will not come back in the uptime table (no ticket means 100% uptime for the serial number therefore the ISBLANK in the measure).

EDIT: As the situation as described above required a many to many relationship (not ideal, messy data...) I've changed the cross filter direction between the tables and I get the correct output now with your measure. Therefore I will accept it as the solution! Thanks a lot and have a great day.