cancel
Showing results for
Did you mean:
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.

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

Fabio

1 ACCEPTED SOLUTION
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])``

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.
5 REPLIES 5
Regular Visitor

Ciao @yingyinr ,

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

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])``

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

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

Announcements

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

Top Solution Authors
Top Kudoed Authors