100% ALLSELECTED or Other Filter

[ElaShaked]

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

 

[ElaShaked]

Thank you for you help :wink:

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 .........................