Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pivot a summarized table with DAX

I have a summarized table that looks like this:

I want to pivot it to a new table that looks like following for current month = 1 

metricsvalues
New MRR456564
Expansion MRR48456
Contraction MRR4545
Churned MRR67878

last month mrr

124545

Total MRR

457575

 

I can't use the pivot function in query editor because this is a summarized table.

And I need the results in a new table for a waterfall chart.

How can I do this?

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I made it work by using Selectcolumns

     

    Pivot MRR = FILTER(
                UNION(
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Beginning MRR","Values",MRRWaterFall[last month mrr]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","New MRR","Values",MRRWaterFall[New MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Expansion MRR","Values",MRRWaterFall[Expansion MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Contraction MRR","Values",MRRWaterFall[Contraction MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Churned MRR","Values",MRRWaterFall[Churned MRR])
                    ),[current]=1
)

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I made it work by using Selectcolumns

     

    Pivot MRR = FILTER(
                UNION(
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Beginning MRR","Values",MRRWaterFall[last month mrr]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","New MRR","Values",MRRWaterFall[New MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Expansion MRR","Values",MRRWaterFall[Expansion MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Contraction MRR","Values",MRRWaterFall[Contraction MRR]),
                    SELECTCOLUMNS(MRRWaterFall,"current",MRRWaterFall[current month],"Metrics","Churned MRR","Values",MRRWaterFall[Churned MRR])
                    ),[current]=1
)

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If you want to display measure on row. Matrix has a property show on row. Use that.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak I don't want to display these values. I need to use them for a waterfall chart.

      Apologies if I wasn't clear in the post. I've edited it to reflect my requirements more clearly.