Measure ALLSELECTED not being affected by columns

[mariogonzra]

 Hi, everyone:

I'm starting in PowerBI and I have a simple doubt that may be easy to resolve, but cannot find anywhere.

I have the following Matrix or whichever visual and I would like to create a mesaure that returns the value for the category "all" to all the lines. However, I cannot seem to get it right. The measure I'm using is the following

age.patients.total = CALCULATE(SUM(Table[patients], FILTER(ALLSELECTED(Table),Table[Age] = "All"). But as you can see below, it will work taking into account the outside filters and the row context but not for the columns (it's adding up the values for the different quarters/columns). I know the ALLSELECTED functions makes the measure to ignore the query filters, but is there any way around this?

Can anyone help?

Thanks,

Mario

[v-xinruzhu-msft · Accepted solution]

Hi,

@Ritaf1983 , thank for your concern about the problem, and i want to offer some more information for user to refer to.

hello @mariogonzra , based on your description, if your date column is based on a date table, you can try the following solution.

Sample data

Create a calendar table and create a one-many relationship between tables.

Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

Then create a measure

age.patients.total =
CALCULATE (
    SUM ( 'Table'[patients] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Age] = "All"
            && YEAR ( [Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) )
            && QUARTER ( [Date] ) = QUARTER ( MAX ( 'Calendar'[Date] ) )
    )
)

Put the following field to the matrix

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @mariogonzra 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523