How can I dynamically display "Year (N = xx)" while respecting slicers and preventing separate bars

[Anonymous]

I am working on a stacked bar chart in Power BI, where I need to show the year along with the total count of jobs (formatted like "SurveyYear (N = xx)"). I want the count to dynamically adjust when a slicer for NameCategory is applied.

Here is the DAX formula I am using to format the count for each SurveyYear:

DAX
FORMAT(vw_Questionnaire[Year], "") & " (N = " & UNICHAR(160) & FORMAT(CALCULATE(count(TableName[TotalJob]),
FILTER(TableName, TableName[Year] = EARLIER(TableName[Year]))), "") & ")"

This formula works as expected but when I select any of the NameCategory in slicer N = ## does not change, So I add the NameCategory filter, like this:

DAX
FORMAT(TableName[Year], "") & " (N = " & UNICHAR(160) & FORMAT(CALCULATE(count(TableName[TotalJob]),
FILTER(TableName, TableName[Year] = EARLIER(TableName[Year])),
FILTER(TableName, TableName[NameCategory] = EARLIER(TableName[NameCategory]))), "") & ")"
... it causes the stacked bar chart to divide into multiple categories for each NameCategory.

I want to keep all NameCategory values aggregated into one bar per Year like stacked bar chart, and display the total count of jobs for the selected year & NameCategory.

[Anonymous]

Hi @Anonymous ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

[Poojara_D12]

Hi @Anonymous 

Use this DAX formula to ensure the count dynamically updates without splitting the stacked bar chart:

FORMAT(TableName[Year], "") & " (N = " & FORMAT(
    CALCULATE(
        COUNT(TableName[TotalJob]),
        ALLSELECTED(TableName[NameCategory]) -- Keeps aggregation across all NameCategory
    ), ""
) & ")"

This keeps the bars aggregated while dynamically adjusting the total count based on slicer selections.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[ValtteriN]

Hi,

I recommend using the data label to achieve this:

E.g.
data:

Measures:

Measure 31 =

var _year = MAX('Calendar'[Year])

var _rank = RANKX(ALL('Table (39)'),[Measure 32],,)

var _xx =

CALCULATE(COUNTROWS('Table (39)'),ALLSELECTED('Table (39)'[Patient]))

RETURN

IF(_rank=1,

_year & " "  & _xx,BLANK())

Measure 32 = COUNTROWS('Table (39)')

Now the labels react to slicers

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/