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.

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