Forum Discussion

SDK0415's avatar
SDK0415
Frequent Visitor
2 years ago
Solved

Dynamic columns in Matrix table based on two slicers

I have a sales table that has 4 fields Region, Prod category, Calendar Month and Net Sales. I would like to see one matrix table in Power BI in such a way that I compare two months of data bas...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, SDK0415 

    Thanks for Uzi2019 and Kishore_KVN reply. The current version of the matrix does not support the use of Measure as a column, you can try the following method to create multiple Measure to be placed in the Card.

    Measure:

    Diviation Electronics = 
    VAR _maxCalendarMonth =
        CALCULATE ( MAX ( 'Table'[Month Number] ) )
    VAR _minCalendarMonth =
        CALCULATE ( MIN ( 'Table'[Month Number] ) )
    VAR _maxMonthElectronicsNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _maxCalendarMonth
                && 'Table'[Prod Category] = "Electronics"
        )
    VAR _minMonthElectronicsNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _minCalendarMonth
                && 'Table'[Prod Category] = "Electronics"
        )
    RETURN
        _maxMonthElectronicsNetSales - _minMonthElectronicsNetSales
    
    Diviation Furniture = 
    VAR _maxCalendarMonth =
        CALCULATE ( MAX ( 'Table'[Month Number] ) )
    VAR _minCalendarMonth =
        CALCULATE ( MIN ( 'Table'[Month Number] ) )
    VAR _maxMonthFurnitureNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _maxCalendarMonth
                && 'Table'[Prod Category] = "Furniture"
        )
    VAR _minMonthFurnitureNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _minCalendarMonth
                && 'Table'[Prod Category] = "Furniture"
        )
    RETURN
        _maxMonthFurnitureNetSales - _minMonthFurnitureNetSales
    
    Diviation% Electronics = 
    VAR _maxCalendarMonth =
        CALCULATE ( MAX ( 'Table'[Month Number] ) )
    VAR _minCalendarMonth =
        CALCULATE ( MIN ( 'Table'[Month Number] ) )
    VAR _maxMonthElectronicsNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _maxCalendarMonth
                && 'Table'[Prod Category] = "Electronics"
        )
    VAR _minMonthElectronicsNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _minCalendarMonth
                && 'Table'[Prod Category] = "Electronics"
        )
    RETURN
        DIVIDE (
            _maxMonthElectronicsNetSales - _minMonthElectronicsNetSales,
            _minMonthElectronicsNetSales
        )
    
    Diviation% Furniture = 
    VAR _maxCalendarMonth =
        CALCULATE ( MAX ( 'Table'[Month Number] ) )
    VAR _minCalendarMonth =
        CALCULATE ( MIN ( 'Table'[Month Number] ) )
    VAR _maxMonthFurnitureNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _maxCalendarMonth
                && 'Table'[Prod Category] = "Furniture"
        )
    VAR _minMonthFurnitureNetSales =
        CALCULATE (
            SUM ( 'Table'[Net Sales] ),
            'Table'[Month Number] = _minCalendarMonth
                && 'Table'[Prod Category] = "Furniture"
        )
    RETURN
        DIVIDE (
            _maxMonthFurnitureNetSales - _minMonthFurnitureNetSales,
            _minMonthFurnitureNetSales
        )
    
    

     

    Best Regards,
    Yang

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.
    Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum