Combining matrix

[Marico]

Hi,

I have the following requirements from the user not sure how many of them are possible.

Model Structure:

Source and Target data are coming from two different database tables. There is a date table as well in the model.

Requirement:

I need to create a matrix like the above where:

1. The user should be flexible in changing the target month column for different months from the target table.

2. A user should have a slicer to choose the blue color code months. They are coming from the source table.

3. The default view should show the current end-of-the-year target column and the previous 5 months from the source table.

Here is the link to my working file - https://github.com/priyam2790/PBI/blob/main/matrix.pbix

Thank you!

[v-mengmli-msft]

Hi @Marico ,

I'm afraid Power BI Desktop doesn't have a slicer like that. The selections in most slicers are manual, or have defaults set at the beginning like the preselected slicer.

I'm using TODAY() to generate a single list that includes the first five months of the current month, so it's dynamic. When it comes to the next month, the preselected slicer will select February and the first five months by default, and the user doesn't need to select them manually.

I can't give any more advice on the target column because I don't know what the logic of its calculation is. Please provide some examples. Since I will have the weekend off and my next reply will perhaps be next Monday, I suggest you try the method I provided first. Include in the next reply any related problems encountered and I will get back to you as soon as I see it.

Best regards,

Mengmeng Li

[Marico]

Hi @v-mengmli-msft 

"I'm using TODAY() to generate a single list that includes the first five months of the current month, so it's dynamic. When it comes to the next month, the preselected slicer will select February and the first five months by default, and the user doesn't need to select them manually."

For the blue highlighted part, when the februrary will come then the slicer and table will have the following months appearing in the table and slicer -> 2024-Sep, 2024-Oct, 2024-Nov, 2024-Dec, 2025-Jan, Current Month - Is this correct understanding? If so, then this is as per the requirement.

Regarding the target data, there are no calculations in this, in the target table for each end of the month, there is a target % [fully % op] associated with it. Please have a look at the target table in the shared file and the description of this post on how the target needs to be merged with the above requirement. 

Please let me know if I need to share more details about the requirements. Thanks.

[v-mengmli-msft]

Hi @Marico ,

I would recommend that you do not display the target column in the Matrix, the easiest way to do this is to display it separately using Card. Because the display of the columns in the Matrix is determined by the preselector slicer, which takes its options from the Month-Year of the date column, this means that if you want to display the Target column in the matrix, you have to add the corresponding row in Month-Year, but the calculation logic of Target is different from that of the other dates, and its relation to the Sales table is difficult to define. All things considered, using cards to display Target values is the simplest and most stable method.

Best regards,

Mengmeng Li

[Marico]

@v-mengmli-msft Thanks for your response. 

Let me check with the client on creating cards for the target values.

Could you confirm the following as well, please?

"I'm using TODAY() to generate a single list that includes the first five months of the current month, so it's dynamic. When it comes to the next month, the preselected slicer will select February and the first five months by default, and the user doesn't need to select them manually."

For the blue highlighted part, when the februrary will come then the slicer and table will have the following months appearing in the table and slicer -> 2024-Sep, 2024-Oct, 2024-Nov, 2024-Dec, 2025-Jan, Current Month - Is this correct understanding? If so, then this is as per the requirement.

[v-mengmli-msft]

Hi @Marico ,

Yes. It is correct understanding.

Best regards,

Mengmeng Li

[Marico]

 @v-mengmli-msft @v-tangjie-msft  I have created the following measure (or separate measure for current month and previous month) to calculate the month-over-month percentage change. How can I calculate this percentage change based on the slicer selection? 

Sales MoM% =

VAR __PREV_MONTH = CALCULATE(

        SUM(Sales[Sales Amount]),

        PREVIOUSMONTH(Sales[Date]),ALL(Date[Month-Year])

    )

  RETURN

    DIVIDE(

        SUM(sales[sales amount]) - __PREV_MONTH,

        __PREV_MONTH

    )

[v-mengmli-msft · Accepted solution]

Hi @Marico ,

According to your description, you want to display the data of the current month and the previous five months, and users can use slicer to change the columns in the matrix. Currently, the built-in slicer does not support the default option. I recommend that you use the preselected slicer. The following is my test for your reference. Since I don't know how your year-end target column is obtained, I only display the data up to the current month.

Step1. Add preselected slicer. 

Step2. Custom table and Measure.

_PreselectedSlicer = DATATABLE(
    "IsDirtySlicer", BOOLEAN, 
    {
        {FALSE()},
        {TRUE()}
    }
)

Measure 2 = 
VAR _select_date = SELECTEDVALUE('Date'[Month-Year])
VAR _previous =UNION(SELECTCOLUMNS(GENERATESERIES(0,5,1),"test",FORMAT(EDATE(TODAY(),-[Value]),"YYYY-MMM")),ROW("test","CurrentYear"))
RETURN
    IF(_select_date IN _previous,TRUE(),FALSE())

Step3. The Matrix.

Buttons of preselected slicer.

Best regards,

Mengmeng Li

[Marico]

Hi @v-mengmli-msft Thank you for your analysis and testing.

Please are a few points from my side:

1. Can we have the solution a bit dynamic that if we select current month then previous 5 months need not to be selected from the slicer and they are auto populated in the matrix, else every month user will have to manually select current plus previous 5 months from slicer?

2. The target values are coming from target table in the file shared earlier on GitHub.

3. It is not possible to accomodate all requirements mentioned in the original post in one matrix then is there any better visualization for this?

Looking forward to your response.

Thank you!

[lbendlin]

Power BI is not Excel.  Mixing columns like this is possible if you create custom matrix visuals but it is very involved and a maintenance nightmare.

Here is an example of how to abuse the column totals for the target value.

Note the changes to the data model.