Set values as Maximum but want to have the grand total sum those values

[Anonymous]

I have a matrix that I have built a financial report into, basically a budget to actual comparison. My issue came about once I started to add multiple locations into to columns field. I am trying to bring in the budget values for each account group (by location). The problem i came across is that the budget is a column on the actuals table, so when i bring it in, its sums it all up and gives me a very large budget number (basically multipling it by how many times that actual occurs). To "fix" this, if i change the value field on budget to maximum (or minimum), it gives me the correct values, but the grand total at the bottom is not the total, it is just the maximum value in that budget column. Is there a way i can still just have that grand total line sum up what is in the budget column, on the matrix. 

If there is another way to get the budget numbers in, i am all ears. I have only just taugh myself this program the past few months, so if there is like a measure that can be done, let me know. I am not too familiary with those just yet, basically ive created new columns and uses lookups or merges to bring that in. 

See below for a screeshot 

[MFelix]

Hi @Anonymous,

In order to help you better can you please explain some thing on your model:

• Do you have two tables (Actuals + Budget) or only one?
• If you have two tables how are they related?
• Do you use the column values on your matrix or do you use measures?

Believe that if you have 2 tables with the correct relationship between both tables you can achieve the desired result, but as said you need to give further inshight on your model setup.

Regards,

MFelix

[Anonymous]

Hi, 

technically i have two tables (budget and actuals) but when trying to create a relationship, it doesnt work because there are no unique values that i can create the relationship on. So to get around this, i have done a merge query on the actuals table to bring the budget values on to that table. In this case i match them by location, department, and account. 

I use column values on my matrix. usually this column would be the values i bring in from the merge or if i do a lookupvalue instead of a merge. 

[MFelix]

Hi @Anonymous,

When you do the merge of both tables the values are repeated that.s why you are getting big values on your matrix.

You need to create a dimension table with unique values and then use this to make the relationship between both tables.

Can you share the structure of your tables and the common fields so I can give you the steps to make it happen?


Regards
MFelix

[Anonymous]

Hi @MFelix,

Here are my Tables structure. I put them into Google Sheets, that was the easiest way i could think of to share them. 

Please let me know if there is something else youd need.

Thanks!  MFelix

[MFelix]

Hi @Anonymous,

can you please tell me what is the column / columns that make the connection between both tables?

Regards,

MFelix

[Anonymous · Accepted solution]

Hi @MFelix

I was actually able to get it to work. Now that PowerBI allows for Many to Many relationships, i was able to get that done and work with that.

Thanks for putting the time into this!

[MFelix]

Hi @analist31,

Glad you were abble to do it but be carefull when setting up many to many relationships and the way cslculations are made.

Regards,
MFelix