Sum actuals by Month (Date) and Contract (text)

[Aimeeclaird]

Please can someone help? Trying to compare my target table to actual sales. 

I'm looking to create a calculated column in my target table that shows the actual sales. I think I need something that will calculate:

Sum total sales, group by month and then by contract 

Each contract has a target for each month in my target table, it looks like this

Contract	Date	Target	Actual
1	Jan 2020	100
1	Feb 2020	50
1	March 2020	70
2	Jan 2020	150
2	Feb 2020	200
2	March 2020	250
3	Jan 2020	300
3	Feb 2020	100
3	March 2020	150

[v-yingjl · Accepted solution]

Hi @Aimeeclaird ,

If I got it correctly, you can create this calculated column to calcuate actual sales for each month:

Actual = 
CALCULATE(
    SUM('Table'[Target]),
    FILTER(
        ALL('Table'),
        'Table'[Date] = EARLIER('Table'[Date])
    )
)

Variance = [Target] - [Actual]

Attached a sample file in the below, hopes to help you.

If I got it wrong, please feel free to let me know so that I could help you better to solve this issue.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @Aimeeclaird ,

You can use append both tables (keeping exactly the same name for columns on both tables). So you will have the target and sales on the same table.

[Aimeeclaird]

Hi @camargos88 

If I append, how would that sum the total sales? I want my target table to have a row per contract, per month. 

I then want to see the total sales for that contract and month beside it. 

Please can you clarify (sorry, PBI is new to me) how appending the sales would allow me to present the target vs the actual? If i append the sales to the target, my targets go up massively as they're duplicated for every sales row?

Many thanks in advance

Aimee

[camargos88]

@Aimeeclaird ,

I can think about 2 options:

1) You can group your sales table by month/contract summing the sales values and merge this table with the target table. Be aware of the join kind (I would say full join) and clean the month/contract columns before the merge.

2) Append both tables, cleaning the month/contract values and same column names for them. 

The 2 option can be useful if you need to counting the values or calculate avg basead on user selections. If you aggregate you tables having 1 row per month/contract maybe you lose this info.

[Aimeeclaird]

I'm sorry @camargos88 , I am not sure if I am explaining what I need correctly, or my lack of PBI knowledge is the problem but I don't understand how either of your ideas help.

My final product should show:

- Month Year

- Total Sales (actual)

- Total Target Sales (target)

- Variance between (target - actual)

Also, how should I merge or apend as there isn't a common ID etc? 

Is there not a suitable calculation I could write into the target table that 'groups' the sum? So instead of summing the entire column, it looks at the Contract and the Date and gives me the total sales for that month/year and contract?

[camargos88]

@Aimeeclaird ,

Check the attached file.