Summarize function support

[Anonymous]

Hello, 

I am essentially trying to recreate the functionality of a pivot table with the below data set. What I want to be able to summarize is by calendar week the # of shipped and cancelled item quantities. Once I have this solved I can add a calculation for a cancellation rate. The data below is built via the summarize function of an existing table. 

The final table should have the following headers: Order_Week, Order_Year, Shipped, Cancelled. With the final two being the sum of item quantities shipped or cancelled for an individual calendar week. The Order_Week column should not have duplicate values. 

Thank you in advance for any support!

[amitchandak]

@Anonymous , In power query you can pivot last 2

https://radacad.com/pivot-and-unpivot-with-power-bi

else have two measures and use iwth others in visual

shipped = calculate(sum(Table[Item Quantity]), filter(Table, Table[Order Status] ="SHIPPED") )

cancelled= calculate(sum(Table[Item Quantity]), filter(Table, Table[Order Status] ="CANCELLED") )

[Anonymous]

@amitchandak thank you for the response. 

I don't believe I can pivot the last 2 columns for this specific table as it is created via the SUMMARIZE function of a separate table. When I go to transform data I can only transform the original data source.

The functions you provided mostly work, however they need an additional filter for week #.

[Anonymous]

Hi @Anonymous ,

What does "they need an additional filter for week #" mean? Does it mean need a filter for week#? If so, you can use the field week# to create a slicer or you can use the Filters pane to apply a visual level filter to visual. you can see the attachment for details.

If my understanding is not correct, please correct me and provide the correct function logic. Thank you.

Best Regards