help: visual with custom dimensions stacked column chart

[askme1217]

i have four tables, orders ( fact),  product, region, category
i need to create stacked column chart. where  x-axis  is count of orders, legend is category  and for y axis i need to custom values as dimension with filters. i should see 4 bars with 

1  bar = All regions

2 bar = Region = 1 with product = A
3 bar  = Region = 2,  Product = B

4 bar = All regions , Product = A  

for these 4 bars on x - axis, i need a100% stacked column chart

[rohit1991 · Accepted solution]

Hi @askme1217 ,
You can achieve this in Power BI using a disconnected “helper” table to create your custom groupings (bars) and then use DAX to calculate the values for each group.

1. Create a Helper Table (Bar Names/Groups):
In Power BI, go to Modeling > New Table and enter:

CustomBars = 
DATATABLE(
    "BarLabel", STRING,
    {
        {"All regions"},
        {"Region = 1 & Product = A"},
        {"Region = 2 & Product = B"},
        {"All regions & Product = A"}
    }
)

2. Create a DAX Measure for Each Bar:

Create a measure that counts orders based on the bar’s definition:

Orders by Custom Bar = 
VAR SelectedBar = SELECTEDVALUE(CustomBars[BarLabel])
RETURN
SWITCH(
    TRUE(),
    SelectedBar = "All regions", CALCULATE(COUNTROWS(Orders)),
    SelectedBar = "Region = 1 & Product = A", CALCULATE(COUNTROWS(Orders), Orders[Region] = 1, Orders[Product] = "A"),
    SelectedBar = "Region = 2 & Product = B", CALCULATE(COUNTROWS(Orders), Orders[Region] = 2, Orders[Product] = "B"),
    SelectedBar = "All regions & Product = A", CALCULATE(COUNTROWS(Orders), Orders[Product] = "A"),
    BLANK()
)

3. Build Your Chart:

• Use CustomBars[BarLabel] for the X-axis.

• Use your new measure (Orders by Custom Bar) for values.

• Use Category as the legend.

• Change the visual to 100% stacked column chart.

[v-pnaroju-msft]

Hi askme1217,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi askme1217,

We wanted to check in and see if the information we provided has helped resolve your issue. If you need any further support, please don't hesitate to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi askme1217,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @FBergamaschi, @rohit1991, and @danextian, for your responses.

Hi askme1217,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter.

We hope the information provided helps to resolve the issue.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[danextian]

Hi @askme1217 

You will most likely need a disconnected table and some DAX and M acrobatics. But without a sample data  (that can be copy-pasted to Excel as a table) and sample result (visually, from the same sample data), it is difficult to propose a solution. 

[rohit1991 · Accepted solution]

Hi @askme1217 ,
You can achieve this in Power BI using a disconnected “helper” table to create your custom groupings (bars) and then use DAX to calculate the values for each group.

1. Create a Helper Table (Bar Names/Groups):
In Power BI, go to Modeling > New Table and enter:

CustomBars = 
DATATABLE(
    "BarLabel", STRING,
    {
        {"All regions"},
        {"Region = 1 & Product = A"},
        {"Region = 2 & Product = B"},
        {"All regions & Product = A"}
    }
)

2. Create a DAX Measure for Each Bar:

Create a measure that counts orders based on the bar’s definition:

Orders by Custom Bar = 
VAR SelectedBar = SELECTEDVALUE(CustomBars[BarLabel])
RETURN
SWITCH(
    TRUE(),
    SelectedBar = "All regions", CALCULATE(COUNTROWS(Orders)),
    SelectedBar = "Region = 1 & Product = A", CALCULATE(COUNTROWS(Orders), Orders[Region] = 1, Orders[Product] = "A"),
    SelectedBar = "Region = 2 & Product = B", CALCULATE(COUNTROWS(Orders), Orders[Region] = 2, Orders[Product] = "B"),
    SelectedBar = "All regions & Product = A", CALCULATE(COUNTROWS(Orders), Orders[Product] = "A"),
    BLANK()
)

3. Build Your Chart:

• Use CustomBars[BarLabel] for the X-axis.

• Use your new measure (Orders by Custom Bar) for values.

• Use Category as the legend.

• Change the visual to 100% stacked column chart.

[FBergamaschi]

Please show a visual example and include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here