topic Re: Calculate Sum by applying Group By and filetr in combination in DAX Commands and Tips

Calculate Sum by applying Group By and filetr in combination

simmi21 — Tue, 17 Nov 2020 11:24:02 GMT

Dear Team,

I want to calculate a sum of column by selecting the earliest date in a group of multiple fields.

Because the values are redundant on the weekly level, while creating a sum function it is aggregating all the values.

Target is to get for M0 : 135672+289857 and same for other M1 and M2

Here is the example:

Any suggetion what might be helpful ?

Re: Calculate Sum by applying Group By and filetr in combination

amitchandak — Tue, 17 Nov 2020 11:28:39 GMT

simmi21 , Not very clear with give data

You can try measures like

firstnonblankvalue(Table[Date], sum(Table[Value]))

calculate(firstnonblankvalue(Table[Date], sum(Table[Value])), allexcept(Table, table[group]))

Re: Calculate Sum by applying Group By and filetr in combination

Fowmy — Tue, 17 Nov 2020 11:31:19 GMT

simmi21

Are you looking for the earliest date 1st or 3rd column and is the earliest in a month?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Calculate Sum by applying Group By and filetr in combination

simmi21 — Tue, 17 Nov 2020 11:42:23 GMT

I want to do the sum of Column 6 in my example for the first values only in a combination of Column 1 & 2.

AS Coulmn 1 is a slicer variable and it can have more than one date field. so for a combination of dates from column 1 , Column 2 (M0, M1, M2) should refelect the sum of unique values for a combination of Column 1 & 2

Re: Calculate Sum by applying Group By and filetr in combination

amitchandak — Tue, 17 Nov 2020 13:06:30 GMT

simmi21 , Try Measure

sumx(summarize(Table,Table[Column1], Table[Column2], Table[Column6]),[Column6])

calculate(firstnonblankvalue(Table[column1], sum(Table[column6])), allexcept(Table, table[column2]))

Re: Calculate Sum by applying Group By and filetr in combination

simmi21 — Tue, 17 Nov 2020 21:30:35 GMT

Thanks Amit, it helped a bit but not completly.

The sumx(summarize(Table,Table[Column1], Table[Column2], Table[Column6]),[Column6]) works in some cases and in some cases giving wrong results.

Is there a way to get firstnonblankvalue by using combination of two columns ?

In my above sample data set

Combination of column 1 & Column 2 should return a sum of distinct value from column 6.

In SQL term it should be -

select sum(distinct(Column6)) , Column1, Column2 group by column1 & column 2