SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dax Measure Formula Query


Dax Measure Formula Query

Author
Message
darrenkelly
darrenkelly
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 595
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.



Attachments
Dax Expression Query.xlsx (33 views, 287.00 KB)
Martin Schoombee
Martin Schoombee
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14277 Visits: 4688
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
darrenkelly
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 595
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



Attachments
Screen All.PNG (23 views, 13.00 KB)
Screen Domestic.PNG (20 views, 12.00 KB)
Screen France.PNG (20 views, 13.00 KB)
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129351 Visits: 21809
If I understood correctly this could be a solution
Cool

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
darrenkelly
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 595
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



Attachments
DAX Example with Tables.pdf (21 views, 200.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search