Calculate the total of each year

[E_]

I'm trying to calculate the total of each year. 

The subrow 'Total of each row' is a simple count function that when I use the option 'Row subtotal' on the visual settings it returns the the total of each column on the row subtotals. 

The subrow 'Total of each year' is a function that I created to calculate this row subtotals for each year so later I can use it for other things.

But it is not returning the total I want for each column year (that are 124966 for 2023 and 16980 for 2024) but the total of everything that is 141946. 

The measure is the following: 

Total of each year = 

CALCULATE(

    COUNTA('Registration'[ID]),

    ALLSELECTED('Registration')

)

[Wilson_ · Accepted solution]

Hi E_,

If you check the documentation, you will see ALLSELECTED explicitly ignores your filters inside your visual.

Since you want to keep the year filter from the visual, try:

Total of each year = 
VAR SelYears = VALUES ( 'Calendar'[Year] )
VAR Result =
CALCULATE (
    COUNTA ( 'Registration'[ID] ),
    ALLSELECTED ( 'Registration' ),
    SelYears
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[E_ · Accepted solution]

Thank you so much for the help, I was struggling for weeks with this problem!!

Why does this SelYears let it keep the total of each year whe I use it in the calculate?

[E_ · Accepted solution]

Thank you so much for the help, I was struggling for weeks with this problem!!

Why does this SelYears let it keep the total of each year whe I use it in the calculate?

[Wilson_ · Accepted solution]

E_,

Thanks for the update, glad that helped.

Essentially, storing the years in the variable keeps a memory of your visual's year filter. Then after you've ignored that original visual filter context by invoking ALLSELECTED, you are reapplying that original filter context on years to the calculation by putting the SelYears variable in a CALCULATE filter argument.

In short, ALLSELECTED ignores your years; saving the years in the variable and adding it to CALCULATE re-applies the years.

Let me know if that doesn't make sense. I will try to explain it differently. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[Wilson_ · Accepted solution]

Hi E_,

If you check the documentation, you will see ALLSELECTED explicitly ignores your filters inside your visual.

Since you want to keep the year filter from the visual, try:

Total of each year = 
VAR SelYears = VALUES ( 'Calendar'[Year] )
VAR Result =
CALCULATE (
    COUNTA ( 'Registration'[ID] ),
    ALLSELECTED ( 'Registration' ),
    SelYears
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.