Forum Discussion

Ito_c's avatar
Ito_c
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • Ito_c's avatar
    Ito_c
    4 years ago

    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.  

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

Ito_c's avatar
Ito_c
Frequent Visitor
4 years ago

Hi Anonymous , both not work. The first return the same value mentioned 40686% and the second return blank.