Calculated table to solve many to many error

[itstech]

(I have only been doing BI for a few months)

I have a sales dashboard that includes realtime transactional data.  I've attempted to add a sales target table (Month End date, Department, Sales Target Value) that compares actual sales (transaction data)  using a matrix, but it returns a many to many error, so the value it returns is a repeating number. 

Would creating a calculated table resolve this?  How would I do that? 

Or is there a different solution? 

Thank you!!!   

[Anonymous]

Hi @itstech ，

Please have a try.

For example, if you want to create a calculated table that shows the sales target value and the actual sales value for each month and department, you could use a formula like this:

Sales Target vs Actual = 
SUMMARIZE(
    UNION(
        SalesTarget,
        ADDCOLUMNS(TransactionData, "Sales Target Value", BLANK())
    ),
    [Month End date],
    [Department],
    "Sales Target Value", SUM([Sales Target Value]),
    "Actual Sales Value", SUM([Sales Volume KL])
)

This formula uses the UNION function to combine the two tables, adding a blank column for the sales target value in the transaction data table. Then it uses the SUMMARIZE function to group the rows by month and department, and calculate the sum of the sales target value and the actual sales value for each group.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Ritaf1983 · Accepted solution]

Hi @itstech 
Take a look at the linked guide of data Bear, it should help :
https://databear.com/adding-targets-in-power-bi-report/

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