Help creating a stacked chart by multiple columns

[nh27]

Hi, I am trying to create a stacked bar chart which shows 4 products currently split by column and there are different phases of the product. I have attached sample data and expected output.

Is there any way to achieve this without having to unpivot the data?

The challenge I have is the data is already gone through ETL but due to new requirements need to make this work. Am I better off creating a reference query and carry out the unpivot transformation there?

[Anonymous · Accepted solution]

Hi @nh27 ,

I made a sample for you with dax.

Measure = SWITCH(SELECTEDVALUE('Table'[Value]),
"Product A",COUNTROWS(FILTER('Table1',NOT(ISBLANK([Product A])))),
"Product B",CALCULATE( COUNTROWS(FILTER('Table1',NOT(ISBLANK([Product B])))),USERELATIONSHIP(Table1[Product B],'Table 2'[Value])),
"Product C",CALCULATE( COUNTROWS(FILTER('Table1',NOT(ISBLANK([Product C])))),USERELATIONSHIP(Table1[Product C],'Table 2'[Value])),
"Product D",CALCULATE( COUNTROWS(FILTER('Table1',NOT(ISBLANK([Product D])))),USERELATIONSHIP(Table1[Product D],'Table 2'[Value]))
)

Best Regards,

Wearsky

[nh27]

Thank you very much, this worked a charm!

[rajendraongole1]

Hi @nh27 - You can create a reference query and then apply the unpivot operation like this:

• Go to Transform Data.
• Right-click your query and choose Reference.
• In the new reference query, select the columns that represent the phases and choose Unpivot Columns.

This will give you a more dynamic and flexible structure, where phases are represented in a single column, and you can easily build stacked bar charts based on these dynamically.

pls check and let know if it works.

[nh27]

Hi, thanks for this. I have already test this method and it worked, just wasn't sure if there was another way to do this via DAX Measures and whatnot.

[rajendraongole1]

create separate measure for each phase in Power BI as like below:

Phase1_Measure = SUM(Products[Phase1])
Phase2_Measure = SUM(Products[Phase2])
Phase3_Measure = SUM(Products[Phase3])