topic Re: SUM by category in DAX Commands and Tips

SUM by category

Anonymous — Tue, 22 Feb 2022 11:00:43 GMT

Hello,

I have a table and i want to create a mesure to calculate sum by each Cat, if i have the same Esp for each Cat and 2 dates i have to take only the mount of the lastdate

Cat	Esp	Mount	Date
A	1	54	05/01/2021
A	1	54	01/01/2021
A	2	54	01/01/2021
C	3	6	01/02/2021

The measure have to give me that :
For A = 54 (Esp=1 & Date=05/01/2021)+54 (Esp=2 & Date=01/01/2021)=>108

Cat	Esp	Mount	Sum (Measure)	Date
A	1	54	108	05/01/2021
A	1	54	108	01/01/2021
A	2	54	108	01/01/2021
C	3	6	6	01/02/2021

Thank for your help!

Re: SUM by category

tamerj1 — Tue, 22 Feb 2022 12:44:50 GMT

Hi Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/GTSytgeJpOM8WoQO
One way to that is by starting with a calculated column that retrieves the last date as per your requirement.

Last Date = VAR CurrentCatEspTable = FILTER ( Data, Data[Cat] = EARLIER ( Data[Cat] ) && Data[Esp] = EARLIER ( Data[Esp] ) ) VAR Result = MAXX ( CurrentCatEspTable, Data[Date] ) RETURN Result

Then we can create our measure

CatSUM = CALCULATE ( SUM ( Data[Mount] ), REMOVEFILTERS(), VALUES ( Data[Cat] ), FILTER ( ALL ( Data ), Data[Date] = Data[Last Date] ) )

The report looks like this

Please let me know if this answers your query. Have a great day!

Re: SUM by category

Anonymous — Thu, 24 Feb 2022 17:36:45 GMT

Hello,

this is the real calculated column :

test ano sim =

var y= CALCULATE(SUMX(ALL('/BIC/OHZOFOCA13'[NNI modificateur condition de paiement]),SUMX(DISTINCT('/BIC/OHZOFOCA13'[Numero commande]),FIRSTNONBLANK('/BIC/OHZOFOCA13'[Montant total de la commande],0))))

return IF( '/BIC/OHZOFOCA13'[MODIF_COND]="YF60 => Y30P" && TOTALYTD(y,'Période'[Période])>20000 || '/BIC/OHZOFOCA13'[MODIF_COND]="YF60 => Y01J" && TOTALYTD(y,'Période'[Période])>20000,1,0)

I try to check if the total value of y is greater than 20000 but when i use this calculated column is not working because its making comparison with each row of the table and not the total .

Re: SUM by category

tamerj1 — Thu, 24 Feb 2022 17:47:12 GMT

How does that relate to your original query?

Re: SUM by category

Anonymous — Fri, 25 Feb 2022 09:23:46 GMT

Hi,

Sorry it's bad manipulation and the wrong query,

I try to use your measure and it's working but if i have two years it's make the total of all years How can i calculate the measure and get the result by year ?
After use the result in a calculated column and found which cat have the total mount by year greter than 100 ?
Thank you for your help

Re: SUM by category

Anonymous — Fri, 25 Feb 2022 09:43:32 GMT

Details : With catSum for CAT =A i have 108 for lastDate =05/01/2021 but if in my dataset i have for cat =A another Esp with lastdate=01/01/2022 the CatSum is gonna be the same and i want a catsum by year and cat too.

Thank you

Re: SUM by category

tamerj1 — Fri, 25 Feb 2022 10:04:28 GMT

Hi Anonymous
Then use this code for be able to curry out your calculations per year

Last Date = VAR CurrentCatEspTable = FILTER ( Data, Data[Cat] = EARLIER ( Data[Cat] ) && Data[Esp] = EARLIER ( Data[Esp] ) && YEAR ( Data[Date] ) = YEAR ( EARLIER ( Data[Date] ) ) ) VAR Result = MAXX ( CurrentCatEspTable, Data[Date] ) RETURN Result

Re: SUM by category

Anonymous — Fri, 25 Feb 2022 11:16:04 GMT

It's not working when i update the measure ^^

Re: SUM by category

tamerj1 — Fri, 25 Feb 2022 11:24:20 GMT

Hi
That was the updated code for the Calculated Column . Hear is the sample file https://www.dropbox.com/t/0m73Tr3O59lXfSSN

Re: SUM by category

tamerj1 — Fri, 25 Feb 2022 11:45:11 GMT

Hi Anonymous
You were absolutely right
this is the updated measure as well

CatSUM = VAR CurrentYear = YEAR ( VALUES ( Data[Date] ) ) RETURN CALCULATE ( SUM ( Data[Mount] ), REMOVEFILTERS(), VALUES ( Data[Cat] ), YEAR ( Data[Date] ) = CurrentYear, FILTER ( ALL ( Data ), Data[Date] = Data[Last Date] ) )

Updated file also here https://www.dropbox.com/t/oQsyHPnF1p2RqnOB