Power BI Matrix - Multiple Measures are Switch values to Rows and Month in Columns, need month wise

Hi sathish719 ,

 

To calculate month-wise differences for multiple measures displayed in rows within a Matrix visual, you need to create a DAX measure that dynamically calculates the difference between months for each measure. Since you have about 30 measures displayed on the row side, it’s best to avoid creating a separate difference measure for each one. Instead, a dynamic DAX measure will handle all measures at once.

First, ensure that your Matrix visual has measures on the row side, months on the column side, and values from your existing measures. To calculate the month-wise difference, create a DAX measure that identifies the current month and the previous month, and then subtracts the values accordingly.

The DAX formula to achieve this looks like this:

Month Difference =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR PrevMonth = CALCULATE(
    MAX('Calendar'[Month]),
    FILTER(ALL('Calendar'), 'Calendar'[Month] < CurrentMonth)
)
RETURN
IF(
    NOT ISBLANK(PrevMonth),
    CALCULATE([Total Value], 'Calendar'[Month] = CurrentMonth) -
    CALCULATE([Total Value], 'Calendar'[Month] = PrevMonth)
)

Replace [Total Value] with your base measure or use a SWITCH function if you are using a dynamic measure that aggregates various KPIs like Total Employee Count, Total Cost, etc.

If you are using a SWITCH-based dynamic measure, you can enhance the formula to calculate differences for each specific measure like this:

Month Difference = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Emp Count", [Total Emp Count] - CALCULATE([Total Emp Count], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Bill Emp", [Total Bill Emp] - CALCULATE([Total Bill Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Onsite Emp", [Total Onsite Emp] - CALCULATE([Total Onsite Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Offshore Emp", [Total Offshore Emp] - CALCULATE([Total Offshore Emp], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Cost", [Total Cost] - CALCULATE([Total Cost], PREVIOUSMONTH('Calendar'[Date])),
    SELECTEDVALUE(Measures[Measure Name]) = "Total Benefits", [Total Benefits] - CALCULATE([Total Benefits], PREVIOUSMONTH('Calendar'[Date]))
)

Once this measure is created, add it to your Matrix visual to display the differences across months. The Matrix will then show the measures in rows, the months in columns, and the calculated differences for each measure dynamically.

For example, if your data includes measures such as Total Employee Count, Total Cost, and Total Benefits across January, February, and March, your Matrix will display the monthly values and the calculated differences like this:

 

Measures	Jan	Feb	March	March vs Feb	March vs Jan	Feb vs Jan
Total Emp Count	100	80	120	40	20	-20
Total Bill Emp	80	60	100	40	20	-20
Total Onsite Emp	40	20	60	40	20	-20
Total Offshore Emp	60	40	80	40	20	-20
Total Cost	1000	900	1200	300	200	-100
Total Benefits	1500	1400	1700	300	200	-100

 

 

In this Matrix, the calculated month differences will be dynamically applied to each measure without needing to create separate difference measures for each one. The dynamic DAX measure ensures that the differences are calculated correctly across all months and measures, saving you time and effort in maintaining your report.

 

Best regards,

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, sathish719 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.Firstly, based on your description, I have created the following test data:

2. Secondly, I have created the following calculated table:

Table2 =
VAR nt =
    SUMMARIZE (
        'Table',
        [Measures],
        [Attribute],
        [Value],
        "index", SWITCH ( 'Table'[Attribute], "Jan", 1, "Feb", 2, "March", 3 )
    )
VAR _summarize1 =
    SUMMARIZE (
        'Table',
        [Measures],
        "Attribute", "March vs Feb",
        "Value",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( 'Table', 'Table'[Attribute] = "March" )
            )
                - CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER ( 'Table', 'Table'[Attribute] = "Feb" )
                ),
        "index", 4
    )
VAR _summarize2 =
    SUMMARIZE (
        'Table',
        [Measures],
        "Attribute", "March vs Jan",
        "Value",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( 'Table', 'Table'[Attribute] = "March" )
            )
                - CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER ( 'Table', 'Table'[Attribute] = "Jan" )
                ),
        "index", 5
    )
VAR _summarize3 =
    SUMMARIZE (
        'Table',
        [Measures],
        "Attribute", "Feb vs Jan",
        "Value",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( 'Table', 'Table'[Attribute] = "Feb" )
            )
                - CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER ( 'Table', 'Table'[Attribute] = "Jan" )
                ),
        "index", 6
    )
RETURN
    UNION ( nt, _summarize1, _summarize2, _summarize3 )

3.Then, I adjusted the sorting:

4.Here's my final result, which I hope meets your requirements.

 

You may need to note that I am not entirely clear on your data structure, so your data structure might differ from mine (in my case, the date column's data structure is a string). If my response does not resolve your issue, I would appreciate it if you could share your data structure with me, especially the composition of the matrix visual object. This will greatly assist us in analysing and providing an appropriate solution to your problem.When uploading a file, please be careful to delete sensitive information.

 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

 

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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