Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX calculation at aggregation level excluding undelying dimension

Hello Experts

 

I have a dataset which shows the customer wise daily sales. I need to show how the commission will look like based on the Proposal

 

The Dataset

 

Day

Customer

Total sales

01/02/2018

Customer1

400

01/02/2018

Customer2

500

02/02/2018

Customer1

500

02/02/2018

Customer2

450

 

 

Proposal 1

 

Customer1

500

+

Total sales of the month

-

200

+

(Remaining sales after subtracting 200 * 0.5)

Proposal 2

 

Customer1

200

+

Total sales of the month

-

100

+

(Remaining sales after subtracting 200 * 0.5)

 

Condition to consider

 

All calculation are happening on monthly level. So calculation should be shown after aggregating the total sales of the month (ie  adding 500, subtracting 200, remaining sales calculation)
 The table to show should show all customers not at customer level as below
 as below

 

 

Month

 

Total Commission

Proposal 1

Jan

 

3175

Proposal2

Jan

 

2785

 

 

Calculation Example

 

Proposal 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Customer1

500

+

Total sales

-

200

+

(Remaining sales * 0.5)

Ex.

500

+

900

-

200

+

('700*0.5)

1550

Customer2

500

+

Total sales

-

200

+

(Remaining sales * 0.5)

Ex.

500

+

950

-

200

+

(750*0.5)

1625

 

 

 

 

 

 

 

 

 

Proposal 2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Customer1

200

+

Total sales

-

100

+

(Remaining sales * 0.8)

Ex.

200

+

900

-

200

+

('700*0.8)

1460

Customer2

200

+

Total sales

-

100

+

(Remaining sales * 0.8)

Ex.

200

+

950

-

200

+

(750*0.8)

1325

 

Please advise how this can be acheived in DAX formula

 

 

Thanks

BJ

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,

    Please perform the following steps.

    1.Create the following columns in your data table.
    Year = YEAR(Data[Day])
    Month = MONTH(Data[Day])

    2. Create a new table using DAX below.

    Newdata = SUMMARIZE(Data,Data[Year],Data[Month],Data[Customer],"total",SUM(Data[Total sales]))


    3.Create the columns below in the Newdata table.
    Date = DATE(Newdata[Year],Newdata[Month],1)
    year-month = Newdata[Year]&"-"&Newdata[Month]

    4.Create many to many relationship between the Newdata table and proposal table.

    5. Create measure in Newdata table.

    Measure = 
VAR Sales = SUM(Newdata[total])
VAR ValueAdd = MAX(Proposal[Add])
VAR ValueSubtract = MAX(Proposal[Subtract])
VAR ValueMultiply = MAX(Proposal[Multiply])
RETURN
Sales+ValueAdd-ValueSubtract+(Sales-ValueSubtract)*ValueMultiply


    6. Create a new table using DAX below.

    Temp = GENERATEALL (
    Proposal,
    VAR proposaldate = Proposal[Date]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( Newdata, Newdata[Date] =proposaldate  ),
            "Total Commission", [Measure],
            "YM",Newdata[year-month]
        )
)


    For more details, please reveiw attached PBIX file.

    Regards,
    Lydia

5 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    from your calculation it seems that proposals are the same for all customers, correct?
    I think the value for Customer 2 & Proposal 2 should be 1550 - if that's the case this should work
    1) add Date to your proposal table - can be first day of the month, e.g. like here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL8gvTswxVNJRMjIwtNA1MAQiIMfUwAAsBCIN9EyVYnUQio3QFRuhKLZQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Proposal = _t, Date = _t, Add = _t, Subtract = _t, Multiply = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Proposal", type text}, {"Date", type date}, {"Add", Int64.Type}, {"Subtract", Int64.Type}, {"Multiply", type number}})
in
    #"Changed Type"

    2) if you don't have one then create a calendar table - e.g. with CALENDARAUTO in DAX, add YEAR and MONTH
    3) join the calendar table with Day for Sales and Date for Calendar
    4) create this measure

    Measure = 
VAR Sales = SUM(Data[Total sales])
VAR ValueAdd = MAX(Proposal[Add])
VAR ValueSubtract = MAX(Proposal[Subtract])
VAR ValueMultiply = MAX(Proposal[Multiply])
RETURN
Sales+ValueAdd-ValueSubtract+(Sales-ValueSubtract)*ValueMultiply

    with the proposal table I added it gives the same values as in your example - with exception of Customer2 & Proposal2 where I get 1550 (also caclulating manually in your example)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Stachu.

       

      You are right , it is 1550.

       

      Prposal       Date (DD/MM/YYYY)               ADD                  Subtract                Multiply

      Proposal 1  01/02/2018                                500                   200                       0.5

      Proposal 1  01/02/2018                                500                   200                       0.5

      Proposal 1  01/02/2018                                500                   200                       0.5

      Proposal 2  01/02/2018                                200                   100                       0.8

      Proposal 2  01/02/2018                                200                   100                       0.8

       

       

      Can you share the PBIX file to have more clarification

       

      Thanks

      Ajbm

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Stachu,

       

      You are right.  It is 1550.

       

      ProposalDate (DD/MM/YYYY)ADDSUBTRACTMULTIPLY
      PROPOsal 101/02/20185002000.5
      PROPOsal 101/03/20185002000.5
      PROPOsal 101/04/20185002000.5
      PROPOsal 201/02/20182001000.8
      PROPOsal 201/03/20182001000.8
      PROPOsal 201/04/20182001000.8

       

      could you please share the pbix file for more clarification.

       

      Thanks

      AJBM

      ,,

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous,

    Please perform the following steps.

    1.Create the following columns in your data table.
    Year = YEAR(Data[Day])
    Month = MONTH(Data[Day])

    2. Create a new table using DAX below.

    Newdata = SUMMARIZE(Data,Data[Year],Data[Month],Data[Customer],"total",SUM(Data[Total sales]))


    3.Create the columns below in the Newdata table.
    Date = DATE(Newdata[Year],Newdata[Month],1)
    year-month = Newdata[Year]&"-"&Newdata[Month]

    4.Create many to many relationship between the Newdata table and proposal table.

    5. Create measure in Newdata table.

    Measure = 
VAR Sales = SUM(Newdata[total])
VAR ValueAdd = MAX(Proposal[Add])
VAR ValueSubtract = MAX(Proposal[Subtract])
VAR ValueMultiply = MAX(Proposal[Multiply])
RETURN
Sales+ValueAdd-ValueSubtract+(Sales-ValueSubtract)*ValueMultiply


    6. Create a new table using DAX below.

    Temp = GENERATEALL (
    Proposal,
    VAR proposaldate = Proposal[Date]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( Newdata, Newdata[Date] =proposaldate  ),
            "Total Commission", [Measure],
            "YM",Newdata[year-month]
        )
)


    For more details, please reveiw attached PBIX file.

    Regards,
    Lydia