'PowerBi - How to show multiple total columns in Matrix?

I have a slicer from which I can select weeks that I want to show on a matrix. Currently, the matrix only shows the total column at the end of all columns basically summing up all the values.

But I want to show multiple total column (One after each month). Like after the End Of Weeks of one month a total column and so on.

enter image description here

matrixpowerbidaxpowerbi-desktopcalculated-columns


Solution 1:[1]

You cannot simply create a Calculated column or Measure to solve this problem. Actually, you can use some logic with Calculated column, but there is a more efficient way. To achieve the goal, you have to create Calculated table based on your date column. You can use that logic to create any custom label for your visuals.

Before we start diving into deep DAX here, let's create a calendar table.

Calendar = 
VAR _minSalesDate = CALCULATE( MIN( Sales[Date] ), ALL( Sales ) )
VAR _maxSalesDate = CALCULATE( MAX( Sales[Date] ), ALL( Sales ) )
RETURN
ADDCOLUMNS(
    CALENDAR( _minSalesDate, _maxSalesDate ),
    "Month", MONTH( [Date] ),
    "MonthYear", MONTH( [Date] ) & "-" & YEAR( [Date] )
)

Now, when we have the table with our dates and additional fields, we can start creating a solution for this particular case.

Create a new calculated table as follows:

CustomCategories = 
UNION(
    SELECTCOLUMNS(
        SUMMARIZE(
            'Calendar', 
            'Calendar'[Date], 
            'Calendar'[Sorter]
        ),
    "Key", 'Calendar'[Date],
    "Label", 'Calendar'[Date], 
    "LabelSorter", 'Calendar'[Sorter]
    ),
    ADDCOLUMNS(
        SELECTCOLUMNS(
            SUMMARIZE(
                'Calendar', 
                'Calendar'[Date], 
                'Calendar'[MonthYear],
                'Calendar'[Sorter]
            ),
            "Key", 'Calendar'[Date],
            "Label", 'Calendar'[MonthYear]
        ),
        "LabelSorter", CALCULATE( MAX( 'Calendar'[Sorter] ), ALLEXCEPT( 'Calendar', 'Calendar'[MonthYear] ) ) + 0.5
    ),
    SELECTCOLUMNS(
        ADDCOLUMNS(
            SUMMARIZE(
                'Calendar',
                'Calendar'[Date],
                'Calendar'[Sorter]
            ),
            "LabelSorter", CALCULATE( MAX( 'Calendar'[Sorter] ), ALL( 'Calendar' ) ) + 1
        ),
        "Key", 'Calendar'[Date],
        "Label", "Total",
        "LabelSorter", [LabelSorter]
    )
)

Note that the last part of above DAX adding Total is optional.

Once the table has been created, go to the Data view, select CustomCategories table, select Label column and sort the column by LabelSorter (you will find an option on the ribbon). Then go back to your Model view and set a relationship between CustomCategories and your Fact table on Date column.

When you have done with all stuff above, switch back to the Report view. Remove the date column from your table visual and replace it with the newly created Label from CustomCategories table.

Now you should see the desired results.

Hope that helps!

Regards, Arek

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 intruderr

Related Questions