How to Pivot a calculated table?

[minhvuong93]

Hello guys,

So I have a Sales Database with 4 key  columns: outlet code, product code, order quantity and month.

My table name is MK.

I would like to pivot the month to the columns and then extract this data to excel, Unfortunately the matrix visual cannot export the data as it is shown.

So I create a table like this using Summarize...but I think this is not the best way.

Using pivot when querying the database is too slow..since I have millions of rows.

Any suggestion would be deeply appreciated.

SummaryMK = SUMMARIZE(MK,MK[OutletCode],MK[ProductCode],"1",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=1),"2",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=2),"3",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=3),"4",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=4),"5",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=5),"6",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=6),"7",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=7),"8",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=8),"9",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=9),"10",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=10),"11",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=11),"12",CALCULATE(SUM(MK[OrderQuantity]),MK[Month]=12))

[v-piga-msft · Accepted solution]

Hi @minhvuong93,

For your requirement, it seems that use R script should be a good choice. I have tests with 20 thousands data, it will spend seconds that should faster than using dax or pivot table.

Before you run Rscript, you should install R engine. The details you could have a look at this article.

You could prepare your data in Power Editor. After installing the R engine, you could click  Run R Script under Transform.

Then you could copy the expression below and paste it in the Script box.

library(reshape2)
result <- dcast(dataset, OutletCode + ProductCode ~ month, fun.aggregate = sum, value.var = "OrderQuantity")

For reference, you could have a look at the pbix I have attached below.

Hope it can help you!

Best Regards,

Cherry

[minhvuong93]

@v-piga-msft

Thanks for your reply,

I havent been able to do it, could you be a little more specifict? 😞

AFter installing: I clicked on the "R" symbol and select "enable".

And then what am I supposed to do next, is it dragiing the column into this?

[v-piga-msft]

Hi @minhvuong93,

Assuming that you have installed the R and enable it.

As I mentioned above, Go to Query Editor, you could click  Run R Script under Transform. Then you could copy the expression below and paste it in the Script box.

library(reshape2)
result <- dcast(dataset, OutletCode + ProductCode ~ month, fun.aggregate = sum, value.var = "OrderQuantity")

Then Close && apply and create the visual.

Best Regards,

Cherry