Forum Discussion

itstech's avatar
itstech
New Member
2 years ago
Solved

Calculated table to solve many to many error

(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!!!   

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.