100% ALLSELECTED or Other Filter

ElaShaked

New Member
Joined
Sep 4, 2014
Messages
7
We have a need to analyze sales by sales type , region and period (Month/Quarter/Year).
When choosing the values from the above filters
the 100% reference should be by the chosen filters and
not by the whole derived data.
For example, If I choose to filter and present Sales order of type Lease, Rent (without type Sold)
for Q 1, Q2 and for regions EMEA & APAC the total percentage per column should sum to 100%.
It means that I would expect to see the data as follows:


----------------------------
Order Type | Period
----------------------------
................| Q1 | Q2
--------------------------
Lease........| 30% | 40%
Rent .........| 70% | 60%
----------------------------
Total ........| 100% | 100%
-----------------------------


I use the following DAX formula:


Bookings:=if(sum(ORDERS[PRICE]) = 0 ,BLANK(), sum(ORDERS[PRICE]) )


Grand Total Bookings by Market Segment:=
calculate(sum(ORDERS[PRICE]),ALLSELECTED(ORDERS[Market Segment]))


% Bookings by Market Segment:=
[Bookings]/[Grand Total Bookings by Market Segment]


but I can’t have the 100% sum by the criteria that I chose – I need to have total
sum of 100% per column as shown in the table above.
I need to have in the formula a parameter that holds the user
filtering values instead of ALLSELECTED.
Can someone suggest for a solution?


Thanks,
Ela
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
Iam not so sure what is Market Segment in your case. If you mean EMEA & APAC then it should not be included in your % bookings by market seg at all as this is what you do not want to see based on above table.
If your second formula would be something like =calculate(sum(ORDERS[PRICE]),ALL(ORDERS[Type]))
you will get the % always summing up to 100% if you select EMEA, APAC or both

 

ElaShaked

New Member
Joined
Sep 4, 2014
Messages
7
Hi Tianbas,
I added new measure with the change,as you suggest.
In both of them I get the same result. ;-(


As I understand from microsoft help
Function ALL remove all related filters and run on ALL data in a cube
ALLSELECTED remove only one (chosen as a parameter) and precerve every other already selected filters.
now I don't understand why get the same result


I checked a measures with several filters and expected from measure with ALL (2) to be greater than ALLSELECTED (1)


1) =calculate(sum(ORDERS[PRICE]),ALLSELECTED(OR;-(DERS[Type))
2) =calculate(sum(ORDERS[PRICE]),ALL(ORDERS[Type]))


what I don't understand? :(
 

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
For your Grand Total measure you need to clear the filter that comes from the column order type in a single row of the pivot (Lease or Rent) to show all types (Lease and rent). An All() with the column Order should do that and leave all other filters like region as they are defined by a Pivotfilter or Slicer. At least this works for me in a simplified model. Maybe your model is a bit more complex. Can you show a bit more especially how the region is included in the Order table and or region table and what is used as slicer/pivot filter
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263

ADVERTISEMENT

This isn't really an answer to your question (and I really need to look further into ALLSELECTED), but I at least want you to have this resource: Clever Hierarchy Handling in DAX – SQLBI

Its an unfortunately reality that "% of parent" is kind of a pain in power pivot.
 

ElaShaked

New Member
Joined
Sep 4, 2014
Messages
7
I'm working with Panorama Necto to display data.
May be the things looks different in it. I will discover it in Excell.

I think my problem is to set group to calculate the measure.

What I want to see is :
when choose some of order types , not all of them I want to see 100% always.
now I can get 100% percent only when choose all order types (all other filters are working for example: country of order or/and date of order )

what I don't get is how to determine a group with not hard coded members, I never know what order type will choose a user.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Your intuition to use ALLSELECTED() sounds correct to me, but without seeing the workbook it's a bit hard to say what is going on.

ALLSELECTED() only removes the filter context from row and columns of the pivot table, not slicers, pivot table filters like Top 10, etc.

The only things I can think might be wrong is you not choosing the correct column in your ALLSELECTED() or, just weirdness with your measures. For example:

I would write
Grand Total Bookings by Market Segment := CALCULATE([Bookings], ALLSELECTED(ORDERS[Market Segment]))

As you have it in your first post... you are treating zero's differently between [Bookings] and the Grand Total.
 

ElaShaked

New Member
Joined
Sep 4, 2014
Messages
7
Hi ,

Here Marco Russo answer to my question :
100% ALLSELECTE

"
The same measure in Excel and Panorama looks different ;-(

You are right when we use Excel to visualize data from cube.

I work with Panorama Necto and it is request to make some additional changes in workboard properties :wink:

"
Thank you Goodman :wink:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top