Forum Discussion

tonk's avatar
tonk
Frequent Visitor
2 years ago
Solved

Data transform from row to column

Hi all,

 

May I know how to transform from the following input to the following output in Power?  I tried to apply pivot.  However I can only select one column either qty or amt.  Thanks!

 

 

 

  • Hi tonk 

     

    Juts use matrix visual .

    use customer and model in a Row section of a Matrix
    Use Month in a column section of a Matrix visual
    then use Sales qty and sales amt in Values section of a Matrix 

     

     

     

    This would give you exact same formatting that you are trying to achieved.

     

     

     

  • 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

2 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion

    Hi tonk 

     

    Juts use matrix visual .

    use customer and model in a Row section of a Matrix
    Use Month in a column section of a Matrix visual
    then use Sales qty and sales amt in Values section of a Matrix 

     

     

     

    This would give you exact same formatting that you are trying to achieved.

     

     

     

  • Alex87's avatar
    Alex87
    Solution Sage

    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