Data at different levels - smarter way to solve this?

[richard-powerbi]

Let's say I have 3 category levels. And I have data with mixed availabillty of data per category. Sometimes I have values in the lowest, middel or highest category. Which basically means that I would either have to sum or devide, depening on the direction. At the end I want to compare data. I can't figure out what's the best approach, I feel there should be a better way. Do I always need to shape this data to bring everything down to the lowest level? So that I can simply sum things up for higher categories in reports? This involves a lot of work and involves many transformations. I'm hoping that there is a smarter way to do this purely by making a smart datamodel, in which - by relation - it can automatically sum or devide depending on the direction and which data is missing. 

In my situation, one table can have data of a different level than another table, or even worse, within one table columns can describe a different level. It feels like a mess and I feel that I need to bring down all data to the same level.

[apiwatTantipor]

Hellow @Richard 

Do you have any recommendation on this thing? 

I'm also looking for the best direction to go on. 

[jdbuchanan71]

Hello @richard-powerbi 

Take a look at this blog post and the article he mentions from SQLBI

https://blog.crossjoin.co.uk/2019/02/24/handling-different-granularities-in-power-bi-using-slicer-gr...

[richard-powerbi]

So if I understand correctly, with ISFILTERED in DAX I can either sum or divide based on which level I'm looking at data in a visual?

If I look at the Excel example from https://www.daxpatterns.com/handling-different-granularities/ then he is only doing SUM to solve this problem, but I would like to divide and spread those advertising values over the dates pro rata Sales.

[dobregon]

assuming you have 2 tables "sales" and "sales reference" 
sales in daily
sales reference in monthly

you will be able to do a relationship between both tables and take some columns
sales reference  column in the sales in daily (so, every day will have the sales ref with the same value of the month)
eomonth for each day, with that you will have the number of days for each month

then you can divide the sales referece by monthdays and you can have your kpi

If you dont want ot divide by the number of days for each month and you want weighted by the sales, you can create a calculated column that sum all the sales by month and then create the other column for slaes reference SalesReferenceWeighted = SalesReferenceMonthly * (DailySales/SumMonthlySales) 

[PaulDBrown]

@richard-powerbi 

there are ways to project summarized data to lower granularity levels. IMHO, I keep clear of those type of solutions UNLESS it is an approved business practice in the organization. (Mainly because you are in effect “fabricating”  data which doesn’t really exist -at least not necessarily how you have projected it - and can lead to wrong assumptions by users of the  report).

Having said that, (FWIW), is your data all coming from the same table? 

A common situation is to have data at different date granularities (for example, sales data on a daily level and sales forecasts/estimates on weekly or montlhy levels. In these cases, things can get complicated if you decide to project data to the lowest common granularity, since factors such as last day of month, seasonality periods, bank holidays etc can influence how you should be projecting the data lower. 

Anyway, a common solution (which does not involve projecting data to lower granularities) is to use bridge tables in your model to be used for slicers and filter context in visuals. 

If, for example, your sales data is on a daily level and your budget or estimates are on a monthly level, you can create a YearMonth table as a bridge table, linked to the calendar table (with a common YearMonth column) for the sales data, and similiarly to the budget/estimate table (which also has a YearMonth column)

[richard-powerbi]

Hello @jdbuchanan71, thx but I'm not sure what you're pointing me at.

I don't want to find the solution purely in DAX. I want to make the model easy, so that DAX will be easy later on.

For as far as I understand now, I'm more interested in https://www.daxpatterns.com/handling-different-granularities/, but still need to explore if that's what I need.