Forum Discussion
Dynamic columns in Matrix table based on two slicers
- Anonymous2 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 TeamIf 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
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