cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stis
Regular Visitor

SUMX and ALLEXCEPT

Hello,

I'm struggling with a specific formula to be implemented in DAX.

My dataset has several dimensions but I have to apply this formula at a certain level of granularity, therefore I have to calculate the formula at "DIM A" and "DIM B" level and then sum up. As per my PowerBI knowledge I should use the SUMX.

The formula is the following:

 

Measure = IF([Check]<>0,([VOL2]-[VOL1])*[GP/L SC1],[GP2]-[GP1])

 

Where "check" can be 0 or 1.

Very important is the fact that measure "GP/L SC1" is calculated at "DIM A" level and therefore I defined it in the following way:

 

GP/L SC1 =  CALCULATE(DIVIDE([GP1],[VOL1],BLANK()),ALLEXCEPT(FT,FT[DIM A]))

 

I then created a measure to perform the SUMX in the following way:

 

Measure SUMX = SUMX(VALUES('distinct'[DIMA_DIMB]),[Measure])

 

My problem is that the total value is not actually the sum of the values calculated at DIM A - DIM B level.

 

Could you please help me?

Of course let me know if my post is not clear.

 

Fabio

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @Stis ,

You can update the formula of measure [Measure SUMX] as below, please find the details in the attachment:

Measure SUMX = SUMX(SUMMARIZE('SAMPLE_FT','SAMPLE_FT'[COUNTRY],'SAMPLE_FT'[TECH],"nCheck",[Check]),[Measure])

SUMX and ALLEXCEPT.JPG

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

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

View solution in original post

5 REPLIES 5
Stis
Regular Visitor

Ciao @yingyinr ,

thanks for your reply.

Here is a sample file with sample data. I tried to reproduce as much as possible my data model.

As you can see the total value of the "Measure SUMX" is not the sum of the values at Country-Tech level. 

 

Fabio

yingyinr
Community Support
Community Support

Hi @Stis ,

You can update the formula of measure [Measure SUMX] as below, please find the details in the attachment:

Measure SUMX = SUMX(SUMMARIZE('SAMPLE_FT','SAMPLE_FT'[COUNTRY],'SAMPLE_FT'[TECH],"nCheck",[Check]),[Measure])

SUMX and ALLEXCEPT.JPG

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

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

View solution in original post

Stis
Regular Visitor

Thank you very much!

I'm including this solution to the real file and let accept it as solution (if it works 😉 ).

Meanwhile thank you again!

 

Fabio

yingyinr
Community Support
Community Support

Hi @Stis ,

OK. Looking forward to your reply. Any comment or problem later, please feel free to let me know. 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yingyinr
Community Support
Community Support

Hi @Stis ,

Whether the other values  are correct except the total value? It is a little difficult to find the cause of incorrect total value problem without sample data and applied visual setting in your report. Could you please provide some sample data in your data model, relationship among tables and correct result in order to make troubleshooting? 

In addition, you can refer the method in the following links to resolve it.

Fixing Incorrect Totals Using DAX Measures In Power BI

Why Your Total Is Incorrect In Power BI

Totals incorrect

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.