SUMIF when having a filter in the dashboard

[Anonymous]

Hi, I am having lots of difficulties with a formula that should be very easy.

This is the model:

attribute_table -> Table AT

id_seasson	seasson
1	Winter
2	Spring
3	Summer
4	Autom

transactional_table -> Table TT

id_seasson	id_person	sales
1	1	100
1	2	250
2	3	300
2	4	250
3	5	150
4	6	500

Notice that both table are related by id_seassons. This relation is already built in DAX.

I would like to show the id_person, sales and the sales for the agregation of the seasson for a filter specify in the dashboard. For example:

Filter in the dashboard seasson = Winter
id_seasson	id_person	sales	seassonal_sales_aggreated
1	1	100	350
1	2	250	350

This is working fine: seassonal_sales_aggreated = CALCULATE(SUM(TT[sales];id_seasson=1). BUT I am not able to find a way to make the filter change as it change in the dashboard and to relate this filter to a field out of the TT table (i.e. AT[seasson]. This would be the idea:

seassonal_sales_aggreated = CALCULATE(SUM(TT[sales];id_seasson=seasson). I know the red part is not ok, but that would be the idea.

Thanks a lot

[Anonymous]

Correction:

This is working fine: fine: seassonal_sales_aggreated = CALCULATE(SUM(TT[sales];TT[id_seasson]=1)

[Anonymous · Accepted solution]

How I have temporarely solve this is creating a new table that is a group by id_seasson (NT = SUMMARIZE (TT;TT[id_seasson];sales;TT[sales])) and then join by TT with NT on TT[id_seasson].

I am sure there is a formula to do this, without this table creation process.

[v-jiascu-msft]

Hi @Anonymous,

The measure will respond to the slicers automatically. We don't need to assign the selected values in a measure. We only need to clear the context of [id_person] in your scenario. Please try the formula below.

seassonal_sales_aggregated =
CALCULATE ( SUM ( TT[sales] ), ALL ( TT[id_person] ) )

Best Regards,