Using ALL function with a Filter

[ecarg_124]

Hi All,

I'm trying to create a DAX formula in order to get the total sales of each segment for each region (Sales (Total Region/Segment)) column. I was able to do this successfully using the ALL function (see first table). However, my dilemma is that i will need to drill down my data differently.

Instead of doing Region >> Segment >> Sku, I need to drill down based on Region >> Grouping (which is a column I added to combine segment & package) >> Sku. However, the total sales that I need is still the same as above where I need the total sales for each segment for each region. See below sample desired output. I tried using the ALL function and i was able to get only the sum of the regions, but i will still need to filter it by segment without adding the SEGMENT in the row. Is this possible?

I have attached a link to the .pbix file. 

Sample .pbix file

Thank you so much in advance for your help!

[amitchandak]

@ecarg_124 ,

Try all except

calculate([sales],allexpecpt(Table,table[region], table[segment]))

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[ecarg_124]

@amitchandak

Thank you so much for the quick response! I tried this formula below.

but the results are still not what i'm looking for. This gives me total Region only, but what i would like to see is the total segment by region. My data set is under the SALES DATA table, but i have look-up tables for Regions and products that are separate. How should i modify your recommendation below? 

Thank you again!

[v-kelly-msft]

Hi  @ecarg_124 ,

Yes,as suggested by @amitchandak ,you need first separate the columns for segments out,then you can make further calculation.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@ecarg_124 , do you Really have values like  Segment 1, Segmeant 2 or they are always concatenated with Package. If so, first separate out that.