Matrix Visual

[janani80]

Hi All,

I am working on matrix visual and I have multiple year data and based on the year slicer I want my data to reflect. Also the header in the matrix should change dynamically. Eg if I select the year as 2024 then the sales, commission & Volumes should show Previous (year- 2023) and the rest should show year as 2024.

Any help on this is really appreciated.

		Filter
		2022
		2023
		2024
	2023	2024
Leader Name	Sales	Commission	Volumes	Sales	Calls	Rejections	Leads
A
B
C
D
E

[danextian · Accepted solution]

Hi @janani80 

This is possible but the approach is not very straightforward. You'll need a disconnected table to hold the name of the measures and sales is sorted twice, being the first one and then after volumes, another column has to be created that has a different value that still appears as sales thus the calculated column in the screenshot below.

Then you will need two more disconnected tables both containing the years - the first one will be used as a dimension while the other one in a slicer.

And then the measures that will bind these unrelated tables in a visual.

Switch Measure = 
VAR SelectedSort =
    SELECTEDVALUE ( 'Calculations'[Sort] )
RETURN
    SWITCH (
        SelectedSort,
        1, [Sales Amt],
        2, [Commission Amt],
        3, [Volumes Amt],
        4, [Sales Amt],
        5, [Calls Amt],
        6, [Rejections Amt],
        7, [Leads Amt]
    )

Dynamic Measure = 
VAR SelectedSort =
    SELECTEDVALUE ( 'Calculations'[Sort] )
VAR SelectedYear =
    SELECTEDVALUE ( Years2[Year] )
RETURN
    IF (
        SelectedSort <= 2,
        CALCULATE (
            [Switch Measure],
            FILTER (
                VALUES ( DateTable[Year] ),
                DateTable[Year]
                    IN VALUES ( Years[Year] )
                        && DateTable[Year] < SelectedYear
            )
        ),
        IF (
            SELECTEDVALUE ( Years[Year] ) = SelectedYear,
            CALCULATE ( [Switch Measure], KEEPFILTERS ( DateTable[Year] = SelectedYear ) )
        )
    )

And here's the output

[v-zhouwen-msft · Accepted solution]

Hi @janani80 ,

If your question is not resolved, you are welcome to continue asking questions. If it is solved please mark the corresponding answer as a solution.

Best Regards

[danextian · Accepted solution]

Hi @janani80 

This is possible but the approach is not very straightforward. You'll need a disconnected table to hold the name of the measures and sales is sorted twice, being the first one and then after volumes, another column has to be created that has a different value that still appears as sales thus the calculated column in the screenshot below.

Then you will need two more disconnected tables both containing the years - the first one will be used as a dimension while the other one in a slicer.

And then the measures that will bind these unrelated tables in a visual.

Switch Measure = 
VAR SelectedSort =
    SELECTEDVALUE ( 'Calculations'[Sort] )
RETURN
    SWITCH (
        SelectedSort,
        1, [Sales Amt],
        2, [Commission Amt],
        3, [Volumes Amt],
        4, [Sales Amt],
        5, [Calls Amt],
        6, [Rejections Amt],
        7, [Leads Amt]
    )

Dynamic Measure = 
VAR SelectedSort =
    SELECTEDVALUE ( 'Calculations'[Sort] )
VAR SelectedYear =
    SELECTEDVALUE ( Years2[Year] )
RETURN
    IF (
        SelectedSort <= 2,
        CALCULATE (
            [Switch Measure],
            FILTER (
                VALUES ( DateTable[Year] ),
                DateTable[Year]
                    IN VALUES ( Years[Year] )
                        && DateTable[Year] < SelectedYear
            )
        ),
        IF (
            SELECTEDVALUE ( Years[Year] ) = SelectedYear,
            CALCULATE ( [Switch Measure], KEEPFILTERS ( DateTable[Year] = SelectedYear ) )
        )
    )

And here's the output

[janani80]

Thanks you. Your solution worked.

[Ritaf1983]

Hi @janani80 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...