cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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

1 ACCEPTED SOLUTION
Super User

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

9 REPLIES 9
Super User

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

Anonymous
Not applicable

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 .

Super User

How does that relate to your original query?

Anonymous
Not applicable

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

Anonymous
Not applicable

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 ?

Super User

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``````
Anonymous
Not applicable

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

Super User

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

Super User

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors