Calculate Sum by applying Group By and filetr in combination

[simmi21]

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 ?

[amitchandak]

@simmi21 , Try Measure

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

or

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

[simmi21]

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

[Fowmy]

@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

[amitchandak]

@simmi21 , Not very clear with give data

You can try measures like

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

or

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

[simmi21]

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