Forum Discussion
SUM by category
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!
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 ResultThen 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!
9 Replies
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 ResultThen 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!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 .How does that relate to your original query?