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 : d...
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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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