cancel
Showing results for
Did you mean:
Frequent Visitor

## SUMX plus ALLEXCEPT returning a wrong value

Hi, I'm working in a small dataset with twelve fiscal weeks. So, I need to sum the values from CalculatedMeasure1 for the CalculatedMeasure2 by Fiscal Week, but the SUMX plus ALLEXCEPT is not working to return expected result.

The dataset:

 Fiscal Week CalculatedMeasure1 (percentile) CalculatedMeasure2 (expected result) 2022_F05 30% 115% 2022_F05 60% 115% 2022_F05 25% 115%

For the
For the CalculatedMeasure1 I'm only divide the Consumed by Budget to return the percentile consumed (not all columns in the table to simplify).

For the CalculatedMeasure2 I'm trying

`CalculatedMeasure2 = CALCULATE(SUMX('Table','Table'[CalculatedMeasure1]), ALLEXCEPT('Table','Table'[Fiscal Week]))`

I'm not sure if this is returning the sum of other Fiscal Weeks in the dataset, but the result is 40686%

1 ACCEPTED SOLUTION
Frequent Visitor

I could solve step back on the dataset and use CTE (Commom Table Expression) in SQL Server side. So, based in solution outside of Power BI DAX, I think the solution is the same in DAX, create a temp table and then apply measure in the aggregated temp table.

3 REPLIES 3
Frequent Visitor

I could solve step back on the dataset and use CTE (Commom Table Expression) in SQL Server side. So, based in solution outside of Power BI DAX, I think the solution is the same in DAX, create a temp table and then apply measure in the aggregated temp table.

Community Support

Hi @Ito_c ,

Try this:

``````CalculatedMeasure2 =
SUMX (
ALLEXCEPT ( 'Table', 'Table'[Fiscal Week] ),
'Table'[CalculatedMeasure1]
)
``````

Or

``````CalculatedMeasure2 =
SUMX (
FILTER (
'Table',
'Table'[Fiscal Week] = SELECTEDVALUE ( 'Table'[Fiscal Week] )
),
'Table'[CalculatedMeasure1]
)
``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Frequent Visitor

Hi @v-jayw-msft , both not work. The first return the same value mentioned 40686% and the second return blank.

Announcements