Windows Server 100% ALLSELECTED or Other Filter -DAX

Discussion in 'General Computing' started by Ela Shaked, Sep 4, 2014.

  1. Ela Shaked

    Ela Shaked New Member

    Joined:
    Sep 4, 2014
    Messages:
    2
    Likes Received:
    0
    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
     
  2. kemical

    kemical Windows Forum Admin
    Staff Member Premium Supporter Microsoft MVP

    Joined:
    Aug 28, 2007
    Messages:
    31,790
    Likes Received:
    1,563
  3. Ela Shaked

    Ela Shaked New Member

    Joined:
    Sep 4, 2014
    Messages:
    2
    Likes Received:
    0

Share This Page

Loading...