Forum Discussion
How to Pivot a calculated table?
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))
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
3 Replies
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
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?
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