DAX calculation at aggregation level excluding undelying dimension

[Anonymous]

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

[v-yuezhe-msft · Accepted solution]

@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

[v-yuezhe-msft · Accepted solution]

@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

[Stachu]

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]

Hi Stachu,

You are right.  It is 1550.

Proposal	Date (DD/MM/YYYY)	ADD	SUBTRACT	MULTIPLY
PROPOsal 1	01/02/2018	500	200	0.5
PROPOsal 1	01/03/2018	500	200	0.5
PROPOsal 1	01/04/2018	500	200	0.5
PROPOsal 2	01/02/2018	200	100	0.8
PROPOsal 2	01/03/2018	200	100	0.8
PROPOsal 2	01/04/2018	200	100	0.8

could you please share the pbix file for more clarification.

Thanks

AJBM

,,

[Stachu · Accepted solution]

hi @Anonymous, here you go
https://1drv.ms/u/s!AjxUGXgGNzCEiVv1OaO3j3HtxS3V

[Anonymous]

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