Forum Discussion
Post PartisanTREATAS with 2 conditions same table
Hi,
The problem is in your time group columns. The values in the two tables don't match.
In Sales you have values:
Whereas in Budget you have:
So when treatas brings the filter over from Sales to budget nothing matches and you get a blank result.
Post PartisanHi bcdobbs
That one is a simple one
Community ChampionCurious about your data model because this seems harder than it ought to be. Your description says
- my sales table has Date, Family, TimeGroup and Net sales
- My budget table, which has been done by day has the Date column, Family, Time Group and Budget value
Do you have a date table and it marked appropriately? And then relationships from the date table to date in the Budget table and MM-YYYY as a field in the date table and related to the sales table? Because if you have those things (along with a proper dimension table for Family) this should be easy peasy. Your measures for this should be simple.
Sales = SUM(SalesTable[Sales])
Budget = SUM(BudgetTable[Budget])
Filter context should do everything else for you. I think your data model is off, which is creating a bunch of unnecessary complexity for you.
Would it be possible to provide a pbix with your data model?
- bcdobbs
Just seen this. Fully agree with littlemojopuppy
- littlemojopuppy
bcdobbs great minds think alike 😉
- littlemojopuppy
pedroccamaraDBI if you look at the pic you posted in response to the article I offered, it's clear that Power BI can't determine the appropriate time period for budget. Try reworking your data model. You should have four tables:
- Date table (must include date and MM-YYYY for each record)
- Family dimension
- Sales. Create relationships between MM-YYYY and the date table AND Family and the Family dimension.
- Budget. Create relationships between date and the date table AND Family and the Family dimension.
From there should be really easy. Use the two measures provided earlier for Sales and Budget. Create your matrix using fields from the date table and Family dimension on the axes. And you should be golden!
Hope this helps!
- pedroccamaraDBI
Hello littlemojopuppy
All checked and accordingly to everything. Date table is marked as a date table. I don't have the family column, nor Type group column as a dimension table because they're a part of sales table.
And my model right now is pretty simple, both my budget and sales table are conencted to dates properly.
In my opinion, the problem is that measure with TREATAS. If i change the column name time group from sales to budget, it shows me correctly the budget by any period and wrong sales. But if that column is from sales, then i have the sales ok but not the budget.
Maybe this measure should be done in another way. I don't know.
And i am very sorry to say but my data are a litle sensitive, at least for public. But i apreciate very much if you send me your email by message to me.
Thanks a lot for all your help
P.S i don't have a Family dimension table not a type group dimension table- littlemojopuppy
I think it would be rare for any of us to do this for fun (except maybe for an interest we might have such as sports). The guys that work for me have instruction to look for as much help as they can get from people in this forum (including me, but they can always ask me at work) but to never, ever share confidential data because that's the kind of thing that gets people fired. So I completely understand. 🙂
Let me try to convince you of this: take 100 records from each of your Sales and Budget tables and drop them into Excel. Change details so you have family of "XYZ" or named after fruits (Apple, Banana, Orange, Mango, etc.) Change the amounts too if you like. Just include all columns from each table and I'll figure this out.
- littlemojopuppy
I want to add that with people in the forum who ask "this is what I want go make it for me" I'm a kind of an ass to, because it's clear they're not trying. You're different: you are trying! So I'm willing at help you as much as possible 🙂