SUMX and ALLEXCEPT

[Anonymous]

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

[Anonymous]

Ciao @v-yiruan-msft ,

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

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

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.

[Anonymous]

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

[v-yiruan-msft]

Hi @Anonymous ，

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

Best Regards

Rena

[v-yiruan-msft]

Hi @Anonymous ,

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.