cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors