Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple Table.Pivot using M or Power Query Editor ?

Hi everyone,

 

I just discover the Table.Pivot of the Power Query Editor. Do you know if is it possible to do a multiple pivot <-> multiple value as you can do with Excel ?

 

I have 5 columns : date, number of unit sales, amount of sales, code product (10 possibility), country destination (5 possibility)

What I am looking for is obtain the number of unit sales and the amount of sales for each combination code product-country into distinct columns (2*10*5=100) + the total per country and per code product.

 

The best solution I found now is to use 12 simple Table.Pivot. For the number of unit sales : first one I pivot the country destination, and then I pivot the code product five times, once in each new destination country column (so I have to copy the code product and choose to not aggregate the value). Then I do the same for the amount of sales... I'm very sure someone have a better solution :D

 

Thanks,

 

Baptiste

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    To get total per product per country,

    Create a new measure

    Measure =
CALCULATE (
    SUM ( Sheet3[unit sales] ),
    ALLEXCEPT ( Sheet3, Sheet3[country], Sheet3[product] )
)
    * CALCULATE (
        SUM ( Sheet3[amount] ),
        ALLEXCEPT ( Sheet3, Sheet3[country], Sheet3[product] )
    )

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    To get total per product per country,

    Create a new measure

    Measure =
CALCULATE (
    SUM ( Sheet3[unit sales] ),
    ALLEXCEPT ( Sheet3, Sheet3[country], Sheet3[product] )
)
    * CALCULATE (
        SUM ( Sheet3[amount] ),
        ALLEXCEPT ( Sheet3, Sheet3[country], Sheet3[product] )
    )

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

       

      Thanks you for your answer! I didn't know the AllExcept() formula, it works very well to get the total (I just don't need to make the product [unit sales]x[amount] cause the amount column is already a total)

       

      Does someone have an idea about my multiple pivot issue ?

       

      Thanks,

       

      Baptiste

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        ALLEXCEPT is a DAX function which removes all context filters in the table except filters that have been applied to the specified columns.

        Learn from this link:

        https://docs.microsoft.com/en-us/dax/allexcept-function-dax

         

        I'm not clear about your scenario but willing to help.

        Could you show an example data and expect output?

         

        Best Regards
        Maggie