Measure for calculating average of each table column depending on the value from another column

[crln-blue]

Hello everyone,

For context, I'm using a pie chart visual to show comparison of the statuses on my data but I have the following set of columns on my table:

Name	Location	Date	Status 1	Status 2	Status 3	Status 4
A	US	11/25/2022	1	2	0	6
A	US	05/09/2022	5	5	2	-4
A	CAN	01/18/2022	6		1
A	US	11/25/2022	-1	4		4
B	HK	05/09/2022		-2	6	-2
B	CH	01/18/2022	2	3		0
B	SIN	11/25/2022	6	7	7	0
B	HK	05/09/2022	-81		9	-8
C	US	01/18/2022		-8	-28	0
D	HK	03/03/2023	6	10		12
E	HK	08/19/2023	9		10	7

Above data is let's say a simplified version of my table (but I got more columns and statuses).

So what I'm aiming is to use the pie chart and use the statuses from the Status columns as my legend. I thought of unpivoting the Status columns in order to get a Status columns and its Values column so I can drag the Status column to the Legend of the pie chart.

My values would be the average of each of the status column.

However, for scaleability, I think that this strategy is not good as this produces additional rows of data because my initial rows will be multiplied by the number of my status columns.

So I created a separate status table (no relationship to my main table):

And my measure is this:

Status Average = 
VAR _Max = MAX( 'Status Table'[Status] )
VAR _avg_status1 = AVERAGE('main table'[Status 1])
VAR _avg_status2 = AVERAGE('main table'[Status 2])
VAR _avg_status3 = AVERAGE('main table'[Status 3])
VAR _avg_status4 = AVERAGE('main table'[Status 4])

VAR _Switch = 
    SWITCH(
        _Max,
         "Status 1", _avg_status1,
         "Status 2", _avg_status2,
         "Status 3", _avg_status3,
         "Status 4", _avg_status4,)
Return _Switch

It just gives a blank data. If I return the variable _Max, it displays all the status from the status table I made. If I input a constant value (say, number 1) on their switch statements, it outputs the constant value but if I get the average from its status column (in the main table), its blank.
screenshot below is when I use a constant number in the switch statement (it shows up):

But when I use my intended calculation (averaging the column), it does not show at all. 

What would be the correct way in this measure?

Thank you very much! ♥

[lbendlin]

Your first action must be to unpivot your data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBDoMgDIZfxXCW0II6OKpb4rLEi9nJ+P6vsRZaoh6W8EMJH/9f2HczmtZ8N5oQne+dB+95Q+IVSIM52hMHvYOkXC/i2nYVnMeVSXQYlRxIjTg3V8dLsmWgE7g4TlQtn3s0n1t1pkLJeblHs4LcgMpt7/WezU4PEfyJthHFLuVdRuf6QefspgDcYayuz+oaHA1Cg6Qj6DeVB70qGR0mJZNCkJs9jh8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Location = _t, Date = _t, #"Status 1" = _t, #"Status 2" = _t, #"Status 3" = _t, #"Status 4" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Location", "Date"}, "Status", "Value")
in
    #"Unpivoted Other Columns"

(How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".)

With that all your calculations become super easy.