Dax Measure Formula Query

  • darrenkelly

    SSCommitted

    Points: 1624

    Hi,

    I think this is easy enough to achieve....

    I have 3 Dimensions linking to a Sales Table. The Sales Table captures the Customer, Year, Market and Value of the Sale and the Dimensions represent the Customers, Years, and Markets associated with the Sales.

    My 3 required measure are, Total Sales, Domestic Sales, Foreign Sales (Non Domestic), which have been written as follows:-

    Total Sales:= CALCULATE(SUM(Sales[SalesAmount]))

    Domestic Sales:= CALCULATE([Total Sales], FILTER('Market','Market'[Market]="Ireland"))

    Foreign Sales:= CALCULATE([Total Sales], FILTER('Market','Market'[Market]<>"Ireland"))

    This works fine until the report is sliced by Market which indicates only the Total Sale for a Market.

    What I wish to produce are measures whereby Total Sales, Domestic Sales and Foreign Sales are not slice able other than my date ie at a summary level?

    I've attached a spreadsheet with this example for ease of reference?

    Any assistance would be greatly appreciated.

  • Martin Schoombee

    SSCoach

    Points: 19010

    Try the "ALLEXCEPT" DAX function, which allows you to remove context filters except the ones you specifically want to include. Reference: https://technet.microsoft.com/en-us/library/ee634795(v=sql.105).aspx

  • darrenkelly

    SSCommitted

    Points: 1624

    Martin,

    Many thanks for taking the time to reply. I've attempted to use AllExcept but I'm either using it wrong or its not working in the manner that I'm after.

    Domestic Sales:=CALCULATE([Total Sales], FILTER('Market','Market'[Market]="Ireland"), ALLEXCEPT(Market,Market[Market]))

    My understanding of the above statement would be that Domestic Sales would not change when Market is sliced. However when I slice on Market that is Ireland, then the measure shows up, but when I slice on France then the measure is removed. Also because of the Slice then the total is also reduced.

    I've added some screen shots which highlights the above.

    Any additional guidance is greatly appreciated.

    Thanks

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    If I understood correctly this could be a solution

    😎

    Foreign Sales X:=CALCULATE

    ( SUM(Sales[SalesAmount])

    ,ALL(Sales)

    ,FILTER(ALL('Market'),'Market'[Market]<>"Ireland")

    )

    Domestic Sales X:=CALCULATE

    ( SUM(Sales[SalesAmount])

    ,ALL(Sales)

    ,FILTER(ALL('Market'),'Market'[Market] = "Ireland")

    )

  • darrenkelly

    SSCommitted

    Points: 1624

    Tables didnt format. I've updated the below in a PDF version where table formatting exists.

    Eiriksson it's not quite what I'm looking for as that will provide a total Sale the same across every year for every person because its over All Sales (I think()ie if I Slice by Name,, I get the total Values of 57, 79 and 136

    Data Set is

    Year Name Market Domestic Sales Foreign Sales Total Sales

    2014 Joan Poland 22 22

    2015 Joan Ireland 24 24

    2016 Bob England 20 20

    2016 Bob Ireland 10 10

    2016 John France 12 12

    2016 Mary England 25 25

    2016 Mary Ireland 23 23

    57 79 136

    Required End Result

    Capture Total, Domestic, Foreign Sales per Year Per Person.

    For example Slice on Year 2016 I would like to get

    Year Domestic Sales Foreign Sales Total Sales

    2016 33 57 90

    Include Name into the Report

    Year Name Domestic Sales Foreign Sales Total Sales

    2016 Bob 10 20 30

    2016 John 12 12

    Mary 23 25 48

    If I Slice my Market "Ireland" I currently get this

    Year Name Domestic Sales Foreign Sales Total Sales

    2016 Bob 10 10

    Mary 23 23

    If I Slice my Market "England" I currently get this

    Year Name Domestic Sales Foreign Sales Total Sales

    2016 Bob 20 20

    Mary 25 25

    What I am looking for would be when Sliced by England.

    Year Name Total Domestic Sales Total Foreign Sales Sliced Market Sale Total Sales

    2016 Bob 10 20 20 30

    Mary 23 25 20 48

    If both Bob and Mary exported to France in addition to England in 2016 by 10 then my table would look like

    Year Name Total Domestic Sales Total Foreign Sales Sliced Market Sale Total Sales

    2016 Bob 10 30 20 40

    Mary 23 35 20 58

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply