Forum Discussion

Rosner_'s avatar
Rosner_
New Member
2 years ago
Solved

wrong result allexcept

hello, 

I would like to get the sum of revenue for each product and seller I have a table visualization with 10 columns

and columns product or seller could hold nulls

when I use the formula:

calculate(sum(revenue) ,allexcept (product, seller))

I'm getting correct answers only when I don't have null in both  columns

if I have null in column seller I'm getting wrong answer or in column product or both

I don't understand why I need to use 

"all" operator and then exclude all the fields that shouldn't aggregate by like this:

calculate(sum(revenue) ,all (col1,col2,..col8))

doesn't the allexcept should work?

if you have other way to do it I'll glad to hear 

**I don't have the ability to create table add column or change the Symantic model

only calculated measure.

thank you

 

 

Need Help
Tips and Tricks
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello Rosner_,

     

    Here's a strategy using CALCULATE with SUM and using ALL and reapplying filters as needed:

    SumOfRevenue = 
CALCULATE(
    SUM('TableName'[revenue]),
    ALL('TableName'),
    VALUES('TableName'[product]),
    VALUES('TableName'[seller])
)

    Hope this helps!

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello Rosner_,

     

    Here's a strategy using CALCULATE with SUM and using ALL and reapplying filters as needed:

    SumOfRevenue = 
CALCULATE(
    SUM('TableName'[revenue]),
    ALL('TableName'),
    VALUES('TableName'[product]),
    VALUES('TableName'[seller])
)

    Hope this helps!