What could be the equivalent DAX of this Power Pivot Search

[0Experience]

Hello Everyone,

In my power pivot table I can use the below fields to search my result

Filters: Name, Category

Rows: Year

Values: Sum of Rate

Now, what could be the equivalent DAX for this search technique?

I  tried 

DAX_SUM = Calculate(Sum(Table[Rate]),
Allexcept(Table,
Tabel[Name],
Table[Year],
Table[Category]))

From my DAX, if I do not summarize the DAX_SUM then I can see the summation for each year, name, and category is okay. But, if I use the Sum of DAX_SUM it is showing very big summation number. 

[lbendlin · Accepted solution]

ALLEXCEPT removes all other filters. It does not modify the filters on your selected columns. 

Not sure what this has to do with search?

[Anonymous · Accepted solution]

Hi, @0Experience 

The method you are currently using and is usually correct to preserve the context of the specified column when summarizing the Rate. However, as Ibendlin said, there is a problem with context, and the problem of incorrect sums when using "DAX_SUM sum" can stem from the way the sum is calculated across the entire dataset without the expected context, and there is an improved approach here

DAX_SUM = SUMX(
    VALUES(Table[Name]) * VALUES(Table[Year]) * VALUES(Table[Category]),
    CALCULATE(SUM(Table[Rate]))
)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

ALLEXCEPT removes all other filters. It does not modify the filters on your selected columns. 

Not sure what this has to do with search?