Forum Discussion
Using ALL function with a Filter
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.
Thank you so much in advance for your help!
4 Replies
Try all except
calculate([sales],allexpecpt(Table,table[region], table[segment]))
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
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!
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.
