Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
minhvuong93
Helper II
Helper II

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))

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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")

Capture.PNG

 

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

 

Hope it can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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")

Capture.PNG

 

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

 

Hope it can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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?

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.

 

Untitled.png

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

Capture.PNG

Then Close && apply and create the visual.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.