Sum of all values in all categories instead of showing only that categories value

[Anonymous]

I've got a simple issue that is really stumping me. I must be missing somethign simple.

I have a fact table of transaction values and each has a cost center (department) associated with it. I also have a lookup table with a "budget" value for each cost center, So, obvously, I just want to chart "actual" amounts vs the "budget" amounts and see which departmetns are over or under budget.

I've attached a screenshot of my budget lookup table, the relationship I set up between tabels, and the results I'm getting. Can anyone point out what I'm doing wrong?

I've tried using "sum" measures for actual and budget instead of using the fields from the table. No luck. I've also double-checked that the data types are correct. My costcenter/department values are all stored as text and my actual and budget numbers are all stored as decimal numbers in dollars.

Odly, whenever I drop my "plan" field into a visualization, if defaults to displaying it as a count of values instead of sum of values. Very suspicious.

Screenshot censored to hide potentially sensitive info.

Resulting visualsbudget/plan lookup tableThe field relatioshpip

[MFelix]

Hi @Anonymous,

When your plan field is formatted as text and not as currency ou number that's why when you place it in a visualization it's a count and not a sum or average, probably that's why when using the SUM formulas it's also not working check the print below to see the different symbols for number, currency and text.

Regards,

MFelix

[Anonymous]

I think you're on the right track here but I can't find anything wrong with the data types. I have text values set as text and dollar values set as decimal numbers. Interestingly, though, I don't see a way to assign the data type Currency in the query editor. I just have these options:

I can't imagine that that's the problem, but perhaps it is.

[MFelix]

What version are you using?

However if the values are in decimal they should work correctly. How are you calculating your measures/columns?

[Anonymous]

I'm using Version: 2.46.4732.581 64-bit (May, 2017).

As for the calculations and measures, I've tried dropping the Actual and Bugdet Fields into the Values well and creating SumActual = sum(Table[Actual]) and SumBudget = sum(Lookup[Budget]) measures. I get the same issue either way.

[PattemManohar · Accepted solution]

Table output

Clustured Column Chart

I tried using CostCentre from Lookup/Dim table instead of using CostCentre field from Transaction/Fact table. It resolved the issue. If we use the CostCentre from Fact table then we are facing the issue that was mentioned above.

Correct me if I am wrong. Cheers !!

[Anonymous]

Thank so much for posting this! I think you're spot on.

I did eventually figure out that was was exactly the problem (or seems to be). Since those tables have a relationship, I don't understand why the problem arrises—it seems like either should work as an axis.

I suspect that is has something to do with there being cost center values in one table that aren't in the other. For example, if there's a cost center with costs rows in the fact table, but that cost center isn't listed with a buget in the Lookup table, something breaks. That's just conjecture on my part, but it's the best I've come up with.

[Anonymous]

I got it to work!

I don't know exactly what fixed it, but I just recreated by Budget/plan lookup table from scratch and double checked all of the data types as I brought the new table in. Then, I recreated by visuals with the new fields and viola.

Still don't know what the deal was, but at least I can move foward now!