DAX calculation at aggregation level excluding undelying dimension

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

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