Forum Discussion

nh27's avatar
nh27
Helper III
1 year ago
Solved

Help creating a stacked chart by multiple columns

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?

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      nh27
      Helper III
      1 year ago

      Thank you very much, this worked a charm!

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      nh27
      Helper III
      1 year ago

      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's avatar
        rajendraongole1
        Super User
        1 year ago

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