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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
GDUb
Advocate I
Advocate I

Pivot Columns with DAX

Greetings, Power BI pros. I'm trying to break a time series SPC chart into stages based on a list of stage start dates that will be added to over time. The end result I'm looking for looks like this: 

GDUb_0-1692649621024.png

 

GDUb_0-1692627590784.png

The way I accomplished this in Excel was with a pivot table that has week starting on rows, and stage on columns, so each time series breaks into one column for each stage (in the case below, 3), like so:

GDUb_1-1692649646661.png

 

GDUb_1-1692627759709.png

 

I'd like to recreate this report using Power BI desktop, but I don't know how to break the single time series column into one column for each stage. Is there a DAX pivot pattern? I know that PowerQuery has pivot capabilities, but I am trying to avoid that.  The key benefit of the pivot in Excel is that it updates properly when the user adds additional stages without any additional modification. Thanks!

8 REPLIES 8
ERD
Community Champion
Community Champion

Hi @GDUb , it's a bit diffent in PBI, but you can use a matrix and a line chart with small multiples:

ERD_1-1692719531837.png

 

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thanks, @ERD. I'll experiment a bit and see what I can come up with. 

GDUb
Advocate I
Advocate I

Thanks, @somebih. Sorry for the confusion. In a nutshell, what I'm trying to do is pivot a row to a column with DAX, turning something like this:

Planning WeekStageSample Data 
8/8/202210.01%
8/15/202210.00%
8/22/202210.29%
8/29/202210.20%
9/5/20221-0.02%
9/12/202210.03%
9/19/20221-0.05%
9/26/20221-0.01%
10/3/202210.22%
10/10/20221-0.03%
10/17/202210.00%
10/24/20221-0.03%
10/31/20221-0.34%
11/7/202220.09%
11/14/20222-0.06%
11/21/202220.14%
11/28/202220.89%
12/5/20222-0.16%
12/12/202220.41%
12/19/20222-0.38%
12/26/20222-0.01%
1/2/202320.32%
1/9/202330.14%
1/16/20233-0.32%
1/23/20233-0.02%
1/30/20233-0.18%
2/6/202330.02%
2/13/202330.06%
2/20/20233-0.00%
2/27/20233-0.01%
3/6/20233-0.01%

 

Into this: 

 Stage  
Planning Week123
8/8/20220.01%  
8/15/20220.00%  
8/22/20220.29%  
8/29/20220.20%  
9/5/2022-0.02%  
9/12/20220.03%  
9/19/2022-0.05%  
9/26/2022-0.01%  
10/3/20220.22%  
10/10/2022-0.03%  
10/17/20220.00%  
10/24/2022-0.03%  
10/31/2022-0.34%  
11/7/2022 0.09% 
11/14/2022 -0.06% 
11/21/2022 0.14% 
11/28/2022 0.89% 
12/5/2022 -0.16% 
12/12/2022 0.41% 
12/19/2022 -0.38% 
12/26/2022 -0.01% 
1/2/2023 0.32% 
1/9/2023  0.14%
1/16/2023  -0.32%
1/23/2023  -0.02%
1/30/2023  -0.18%
2/6/2023  0.02%
2/13/2023  0.06%
2/20/2023  0.00%
2/27/2023  -0.01%
3/6/2023  -0.01%

 

I'm looking for a general solution where the number of stages is changing/unknown, so a brute force formula that explicitly names the stages, such as the one suggested at Solved: Re: Convert multiple column data into a column wit... - Microsoft Fabric Community, will not suffice. 

 

Thanks!

Hi @GDUb after I inserted data into Excel file, imported into pbi file, in Power Query I did transformation as on Pic1, then on tab Transform choose as on Pic 2 and final step is Pict 3.

Did I answer correctly? Kudos appreciate / accept solution.

 

Pic 1

some_bih_0-1692728990925.png

Pict 2

some_bih_1-1692729074408.png

 

Pic 3 final (you can replace and rename as you wish)

some_bih_2-1692729199811.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi, @some_bih, I know that PowerQuery has pivot capability. I'm trying to avoid using that as I suspect it may break the fold. I'm looking for a DAX solution. 

GDUb
Advocate I
Advocate I

Hi @some_bih. Thanks for your reply, but I'm not sure I follow. How do statistical functions help me break single data columns into three separate columns by stage? 

Hi @GDUb on your picture I saw Mean





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @GDUb on the link below you can find list of Statistical function, go to single one and check example. 

https://dax.guide/functions/statistical/ 

Did I answer your question? Kudos appreciated / accept solution!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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