July 5, 2016 at 7:13 am
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.
July 5, 2016 at 1:10 pm
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
July 11, 2016 at 5:19 am
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
July 11, 2016 at 7:58 am
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")
)
July 11, 2016 at 1:21 pm
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
YearNameMarketDomestic SalesForeign SalesTotal Sales
2014JoanPoland2222
2015JoanIreland2424
2016BobEngland2020
2016BobIreland1010
2016JohnFrance1212
2016MaryEngland2525
2016MaryIreland2323
5779136
Required End Result
Capture Total, Domestic, Foreign Sales per Year Per Person.
For example Slice on Year 2016 I would like to get
YearDomestic SalesForeign SalesTotal Sales
2016335790
Include Name into the Report
YearNameDomestic SalesForeign SalesTotal Sales
2016Bob102030
2016John1212
Mary232548
If I Slice my Market "Ireland" I currently get this
YearNameDomestic SalesForeign SalesTotal Sales
2016Bob1010
Mary2323
If I Slice my Market "England" I currently get this
YearNameDomestic SalesForeign SalesTotal Sales
2016Bob2020
Mary2525
What I am looking for would be when Sliced by England.
YearNameTotal Domestic SalesTotal Foreign SalesSliced Market SaleTotal Sales
2016Bob10202030
Mary23252048
If both Bob and Mary exported to France in addition to England in 2016 by 10 then my table would look like
YearNameTotal Domestic SalesTotal Foreign SalesSliced Market SaleTotal Sales
2016Bob10302040
Mary23352058
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply