Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bamak
Helper V
Helper V

SUMX no good CALCULATION TOTAL

Hi

 

I want to display the cost of my projects according to the Max date selected in the slicer

 

Project nameCost amountStart Date
PRJ17501/03/2019
PRJ18905/03/2019
PRJ29501/03/2019
PRJ29905/03/2019
PRJ33301/03/2019

 

But the total is incorrectly displayed in Power BI

 

InkedAnnotation 2019-03-05 175038_LI.jpg


This certainly comes from not taking into account the cost of the project 3
I guess my SUMX formula has an error

 

Max_SelectedDate = MAXX(ALLSELECTED(Feuil1[Start Date]); Feuil1[Start Date])
Max Date Cost = SUMX(FILTER(Feuil1;Feuil1[Start Date]=[Max_SelectedDate]);CALCULATE(SUM(Feuil1[Cost amount])))
 
Thanks a lot for help
 
Regards
1 ACCEPTED SOLUTION

Hi

 

I found a solution

 

Manage a new colnmn 

 

MaxDateStart = IF(Feuil1[Start Date]=CALCULATE(Max('Feuil1'[Start Date]);ALLEXCEPT(Feuil1;Feuil1[Project name]));1;0)
 
Now ...   Max Date Cost3 = SUMX(FILTER(Feuil1;Feuil1[MaxDateStart]=1);CALCULATE(SUM(Feuil1[Cost amount])))
 
For me SUMX with filter run better (good total )  with a criterion in "hard" (not a variable)
 
Regards

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @Bamak ,

 

Actually it is not an issue of the SUMX function. Here we should create another calculated table to work on it.

 

Table = VALUES(Feuil1[Start Date])

Afther to create the measures as below.

 

Max_SelectedDate = MAXX('Table','Table'[Start Date])
Measure = SUMX(FILTER(Feuil1,Feuil1[Start Date]=[Max_SelectedDate]),'Feuil1'[Cost amount])

Capture.PNG

 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi

 

I found a solution

 

Manage a new colnmn 

 

MaxDateStart = IF(Feuil1[Start Date]=CALCULATE(Max('Feuil1'[Start Date]);ALLEXCEPT(Feuil1;Feuil1[Project name]));1;0)
 
Now ...   Max Date Cost3 = SUMX(FILTER(Feuil1;Feuil1[MaxDateStart]=1);CALCULATE(SUM(Feuil1[Cost amount])))
 
For me SUMX with filter run better (good total )  with a criterion in "hard" (not a variable)
 
Regards

Thank you very much 

 

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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