Forum Discussion
Data transform from row to column
- 2 years ago
In order to have this exact output, you need to use a table with Customer & Model and then add DAX measures for each month.
Oct-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Oct-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0 Nov-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Nov-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0 Dec-23 Sales Qty = CALCULATE( SUM(YourTable[Sales Qty]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0 Dec-23 Sales amt = CALCULATE( SUM(YourTable[Sames amt]), FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0My recommendation is to avoid this output as it is not scalable and not visually clear.
I would opt for something more dynamic and clear like
Using a matrix with Customer & Model on rows, Month on column and a simple sum measure for Qty & Sales Amount on Values
I would activate in the format pane Values/Options/ Switch values to rows ON. The output is scalable, easier to maintain and clear.
If it answers your query, please mark my reply as the solution. Thanks
In order to have this exact output, you need to use a table with Customer & Model and then add DAX measures for each month.
Oct-23 Sales Qty =
CALCULATE(
SUM(YourTable[Sales Qty]),
FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0
Oct-23 Sales amt =
CALCULATE(
SUM(YourTable[Sames amt]),
FILTER( YourTable, MONTH(YourTable[Month]) = 10))+0
Nov-23 Sales Qty =
CALCULATE(
SUM(YourTable[Sales Qty]),
FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0
Nov-23 Sales amt =
CALCULATE(
SUM(YourTable[Sames amt]),
FILTER( YourTable, MONTH(YourTable[Month]) = 11))+0
Dec-23 Sales Qty =
CALCULATE(
SUM(YourTable[Sales Qty]),
FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0
Dec-23 Sales amt =
CALCULATE(
SUM(YourTable[Sames amt]),
FILTER( YourTable, MONTH(YourTable[Month]) = 12))+0
My recommendation is to avoid this output as it is not scalable and not visually clear.
I would opt for something more dynamic and clear like
Using a matrix with Customer & Model on rows, Month on column and a simple sum measure for Qty & Sales Amount on Values
I would activate in the format pane Values/Options/ Switch values to rows ON. The output is scalable, easier to maintain and clear.
If it answers your query, please mark my reply as the solution. Thanks