Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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

CatEspMountDate
A15405/01/2021
A15401/01/2021
A25401/01/2021
C3601/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

CatEspMountSum (Measure)Date
A15410805/01/2021
A15410801/01/2021
A25410801/01/2021
C36601/02/2021

 

Thank for your help!




1 ACCEPTED SOLUTION
tamerj1
Super User
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
1.png
Please let me know if this answers your query. Have a great day!

View solution in original post

9 REPLIES 9
tamerj1
Super User
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
1.png
Please let me know if this answers your query. Have a great day!

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 .
 

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 ?
Thank you for your help

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

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

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors