topic sum values less than number selected from slicer in DAX Commands and Tips

sum values less than number selected from slicer

Anonymous — Fri, 06 Oct 2023 23:07:07 GMT

Hi All,

I have two tables Data & Sequence Dim. Both tables are linked on Sequence. In reality it's many to one join, but it's basic sample so you can see one to one relation.

Data table:

Sequence Dim table:

Requirement:

When I select any number from Sequence field in Sequence Dim table, I need to check that Sequence field in Data table and pick all the number less than or equal to selected number for each Type and apply sum over that Value.

for eg. When I select number 5 from Seqence in Sequence Dim table, I need to show below output

Type	Value	check <=5 Sequence in data table
a	45	1+2+4
b	95	3+4+5
c	215	2+3+4

When I select number 3 from Seqence in Sequence Dim table, I need to show below output

Type	Value	check <=3 Sequence in data table
a	20	1+2
b	40	3
c	125	2+3

Similarly, user can select any number and dynamically report should reflect the values based on selection

Note: I don't want to break or disconnect relationship between the table.

Re: sum values less than number selected from slicer

kushal_chawda — Sat, 07 Oct 2023 20:18:11 GMT

Anonymous If I understood it correctly you can try below DAX

sum of value = VAR selected_sequence = SELECTEDVALUE('Sequence Dim'[Sequence]) VAR sum_value = CALCULATE(SUM(Data[Value]), REMOVEFILTERS('Sequence Dim'), KEEPFILTERS(Data[Sequence]<= selected_sequence)) RETURN sum_value

Re: sum values less than number selected from slicer

Anonymous — Sun, 08 Oct 2023 19:31:43 GMT

kushal_chawda that works

Re: sum values less than number selected from slicer

sagarp — Fri, 16 Feb 2024 17:46:39 GMT

Hi Kushal,

I have Fiscal Year and Fiscal Week column, and If I select any fiscal week from slicer then it give me sum from First fiscal week to selected week on slicer. So, your trick worked for me. But I also want to get same data for last year. Can you help me with that?

For example, In slicer we select Fiscal Year 2024 Week 5, then based on your DAX code I got the sum of sales for Fiscal week 1 to Fiscal week 5. And I want to compare this data with sum of sales of Fiscal year 2023 from Week 1 to 5.