Show two measures in one matrix (based on index with average) or create a new single measure for it?

[Cookie_Lover]

Hello,

I have two measures in two matrixes. Now, I want to have these two measures as columns in one matrix, because the categories are the same. What I did was to build an average for green and set a index for it, which is 100. Now the values for blue and red should be indexed based on the new Green Total Average.

The last two measures are displayed in the matrixes:

Average Value = AVERAGE(Table1[Value])

Average Green = 
CALCULATE(AVERAGE(Table1[Value]),FILTER(Table1,Table1[Color] in {"very light green","light green","green","dark green","very dark green"}))

Green in whole column in filter context = 
DIVIDE ([Average Value],
CALCULATE(
    AVERAGE(Table1[Value]),
    ALLSELECTED(Table1[Product],Table1[Year]),
        Table1[Color] in {"very light green","light green","green","dark green","very dark green"},
         Table1[Product] = MIN (Table1[Product])
            &&
            Table1[Year] = MIN (Table1[Year])

),0)*100

Green Total Index 100 = DIVIDE (Table1[Average Green],Table1[Average Green],0)*100

Data:

Product	Year	Color	Value
Smartphone	2025	very light green	3
Smartphone	2025	light green	5
Smartphone	2025	medium green	4
Smartphone	2025	dark green	7
Smartphone	2025	very dark green	8
Smartphone	2025	blue	3
Smartphone	2025	red	5
Laptop	2025	very light green	10
Laptop	2025	light green	1
Laptop	2025	medium green	2
Laptop	2025	dark green	53
Laptop	2025	very dark green	8
Laptop	2025	blue	6
Laptop	2025	red	9
Smartphone	2024	very light green	63
Smartphone	2024	light green	32
Smartphone	2024	medium green	65
Smartphone	2024	dark green	86
Smartphone	2024	very dark green	21
Smartphone	2024	blue	22
Smartphone	2024	red	11
Laptop	2025	very light green	1
Laptop	2025	light green	6
Laptop	2025	medium green	8
Laptop	2025	dark green	9
Laptop	2025	very dark green	4
Laptop	2025	blue	5
Laptop	2025	red	6

[Cookie_Lover]

Here is a mockup of the desired result:

[FBergamaschi · Accepted solution]

To get what you ask you need to create a dedicated measure for blue and one for red

Blue = 
CALCULATE(AVERAGE(Table1[Value]),Table1[Color] ="Blue")

Red = 
CALCULATE(AVERAGE(Table1[Value]),Table1[Color] ="Red")

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Cookie_Lover]

Thank you, but I think there must be somehow the year included in the measure.

[Ashish_Mathur]

Hi,

Sorry but cannot understand what you want.  Is it possible for you to put this data in an MS Excel file and show the expected result with your formulas there?  Share the download link of the Excel file.

[Cookie_Lover]

I posted the pbix file and a mockup of the desired matrix in this thread. Here is the excel file:

Combine Measures.xlsx

[Cookie_Lover]

Hi @FBergamaschi,

I want one matrix with the three columns 'Green Total Index 100', 'blue' and 'red'.

[amitchandak]

@Cookie_Lover , You can have an additional column in the Table, which gives three Values- Green, Blue,and  Red. 
Now we can have column or measure avg

This will kind of fix 
Column Avg = averageX(Filter(Table, [Product] = earlier([Product]) &&  [Year] = earlier([Year]) &&  [New Color col] = earlier([[New Color col]) ), [Value]) 

Measure = averageX(Filter(allselected(Table), [Product] = max([Product]) &&  [Year] = max([Year]) &&  [New Color col] = max([[New Color col]) ), [Value]) 

Assume you have two measure and you want to use them as dimensions, create calculation group for those  measures 
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[Cookie_Lover]

@amitchandak , Can you expain, what you mean with [New Color col]? Furthermore, I think there are some mistakes in your two measures, when in put them in Power BI. Sorry, I am a beginner in DAX an can not fix it by myself. Alternatively, here is the pbix file. Maybe someone can build the desired matrix.

Example_Combine_Matrixes.pbix

[FBergamaschi]

OK

Put the color column in the Rows section of the matrix and the measure Green Total Index 100 in Values

Does that work?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Cookie_Lover]

This does not work. I posted the pbix file in this thread, maybe you can try it directly in the file.

[FBergamaschi]

Hi @Cookie_Lover 

I do not understand what you are asking