Flag Indicator doesn't work with Stacked Bar Chart

[abczpo1]

Hi, I would like to seek for some assistance regarding the following issue.

Context:

My dashboard contains of the following KPIs, which are both calculated measures A (which is calculated by dividing sum of variable A_1 / variable A_2)) and B (which is calculated by dividing sum of variable B_1 / variable B_2) which I am tracking on a monthly / YTD basis. A comparison is made

• Whether A manages to hit the minimum value (say $2)

I created a flag to flag out whether A is different from B, which generates different values depending on the month. 

• Say flag = "Hit $2" (can be across multiple months in a year, and multiple categories, such as Retail, Hospitality, Consumer Goods) or flag = "Do not Hit $2"
• This flag should respond and flag as each variable depending on the months selected and multiple categories

Now, I want a bar chart that has two different colurs, recording the count for each indicator

But when I use this flag within the legend for a bar chart, it generates multiple records when selecting multiple months.

• I would like the chart to reflect - across different categories, count the number of records (and legend to indicate whether records "Hit" or "Do not Hit")

[DataNinja777]

Hi @abczpo1 ,

It sounds like the issue is with the measure you're using for the bar chart's values. You're likely counting the raw rows in your data, which causes the chart to create separate records for each month when multiple are selected. To fix this, you need to create a new measure that specifically counts the number of aggregated monthly outcomes ("Hit" or "Do not Hit") rather than the underlying data points.

For a tool that uses DAX like Power BI, you can solve this with a few key measures. First, ensure you have your base KPI measure defined, which calculates your ratio.

KPI A = DIVIDE( SUM(YourTable[A_1]), SUM(YourTable[A_2]) )

Next, create the flag as a separate measure. This will be used in the chart's legend to dynamically categorize the results based on the context of each month and category.

Performance Flag = IF( [KPI A] >= 2, "Hit $2", "Do not Hit $2" )

The most important step is to create the measure that will go into the chart's Values field. This measure must count the number of unique monthly instances, not individual transaction rows. Using SUMMARIZE is an excellent way to create a virtual table of the relevant months and categories, which you can then count. Using a proper Calendar Table in your data model is highly recommended for this.

Monthly Instance Count = COUNTROWS( SUMMARIZE( YourTable, 'YourTable'[Category], 'Calendar'[YearMonth] ) )

Once these measures are ready, you can build your chart. Place your YourTable[Category] field on the Axis, drag the [Performance Flag] measure to the Legend, and use your new [Monthly Instance Count] measure for the Values. This configuration forces the calculation to first determine the status for each month within a category and then correctly count how many months fall into the "Hit" and "Do not Hit" groups, giving you a single, properly stacked bar per category across all selected months.

If you are using a different tool like Tableau, you can achieve the same logic by using a Level of Detail (LOD) expression to calculate the KPI for each specific month and category. You would then create your flag based on that LOD result and use a distinct count of months for the size of your bars, with the flag on the color mark.

Best regards,

[FBergamaschi]

Please 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