cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamak Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Bamak Frequent Visitor
Frequent Visitor

Re: SUMX no good CALCULATION TOTAL

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
3 REPLIES 3
Community Support Team
Community Support Team

Re: SUMX no good CALCULATION TOTAL

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 other members find it more quickly.
Highlighted
Bamak Frequent Visitor
Frequent Visitor

Re: SUMX no good CALCULATION TOTAL

Thank you very much 

 

 

 

 

 

 

Bamak Frequent Visitor
Frequent Visitor

Re: SUMX no good CALCULATION TOTAL

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