cancel
Showing results for
Did you mean:
Frequent Visitor

## Level of Detail Calcs

Hey Guys,

For those familiar with Tableau.....does Power BI have a way to do level of detail calculations?

http://www.tableau.com/LOD-expressions.

Any help would be appreciated.

Thanks,

Joe

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Level of Detail Calcs

Yes, in Power BI this is done through DAX filter expressions combined with measures, for example:

`= CALCULATE( SUM('ResellerSales_USD'[SalesAmount_USD]),ALL('ResellerSales_USD'))`

A measure defined this way would give you the SUM of [SalesAmount_USD] regardless of any visual-level filters/selections. There are a number of these types of filter functions, ALL, ALLEXCEPT, etc.

Proud to be a Datanaut!

22 REPLIES 22
Super User

## Re: Level of Detail Calcs

Yes, in Power BI this is done through DAX filter expressions combined with measures, for example:

`= CALCULATE( SUM('ResellerSales_USD'[SalesAmount_USD]),ALL('ResellerSales_USD'))`

A measure defined this way would give you the SUM of [SalesAmount_USD] regardless of any visual-level filters/selections. There are a number of these types of filter functions, ALL, ALLEXCEPT, etc.

Proud to be a Datanaut!

Highlighted
New Member

## Re: Level of Detail Calcs

I am not sure if you ever used tableau but the formula I would use is { FIXED [ItemLoc],[Period]: SUM([ActualDemand UNITS ])}. How do I translate this into a Dax function? I tried =calculate(sum( vdemand[actual demand Units], Vdemand[ItemLocation], vdemand[period]) but recieve an error that itemlocation cannot be turned into a true or false statement.

Super User

## Re: Level of Detail Calcs

@Bmsams you have to specify the filter conditions - let me know if this helps

= CALCULATE ( SUM(vdemand[actual demand Units]), vdemand[ItemLocation]="USA", vdemand[period]=2015)

Regular Visitor

## Re: Level of Detail Calcs

LOD allows me to specify level of a hierarchy. For example, when the sum is shown at country level, we can define whether the sum to be from state level or city level. i.e we specify country as parameter instead of a specific country name like USA for filtering. So LOD is more than filtering and controls from which level to sum up in a hierarchy. Pl help me showing the relevant DAX

Super User

## Re: Level of Detail Calcs

When you put Country and a simple SUM(Table[Column]) with no filters Measure in a Matrix - that's what you'll get

after Country you can add State then add City add Zip - whatever you wish - the Matrix will break them down for you without need for a new Measure

I thought the question was to how calculate only 1 specific SUM

Regular Visitor

## Re: Level of Detail Calcs

Hi Sean,

you are right when my bottom up data from zip level is same as the bottom up data from city level. but if my city level data (which is correct and authenticated) is not equal to sum of my zip level data (many times the data at lowest granulartiy may not be fully correct), stil the visual at country level shows summation of zip level data which is wrong.

In LOD expression, if i specify city, the country level visual shows data aggregated from city level and not zip level. further drill down to state also presents me with city level aggregation only.

Can i understand the same functionality from your statement also?

Established Member

## Re: Level of Detail Calcs

@SatyaVaitla I think you may need to check out the "X" functions. SumX, AverageX etc allow you to specify the granularity of an aggregation.

A simple example being that using AverageX would allow you to calculate the average of the totals rather than the average of the underlying values

I've not used Tablaeu much but from what I have seen PBI is at least as powerfull in this area and for me at least easier to get my head around.

Regular Visitor

## Re: Level of Detail Calcs

Thanks itchyeyeballs.The link is useful but LOD also help us drill down of heirarchy even if every node is leaf node in the entire hierarchy. For example, the following simple project hierarchy which contains budget at every level is not equal to the sum of the lower level values. Also the budget is not fully distributed at this point of time. With LOD expression, i can create a drill down graph easily that shows values at every level correctly (i could not see do not summarise property in filer properties area).

Still i could not figure out the same power bi desktop. help me to get the same. Then i will add the other complexity to this. This is my real life requirement in SAP project system.

 Project 1level 2level 3level 4level budget Power Plant 1800 Power Plant Civil 500 Power Plant Civil Buildings 300 Power Plant Civil Buildings Office 100 Power Plant Civil Buildings Office Signature Tower 100 Power Plant Mechanical 900 Power Plant Mechanical Boiler 300 Power Plant Mechanical Boiler Generator 100 Power Plant Mechanical Boiler Generator Turbine 100 Power Plant Mechanical Boiler Generator Chimney 100 Power Plant Electrical 800 Power Plant Electrical Switch Yard 600 Power Plant Electrical Switch Yard Transmission Line 500 Power Plant Electrical Switch Yard Transmission Line Plant Area 100 Power Plant Electrical Switch Yard Transmission Line Generation Area 100 Power Plant Electrical Switch Yard Transmission Line Colony Area 100 Power Plant Electrical Switch Yard Transmission Line Office Area 100

Super User

## Re: Level of Detail Calcs

Are these numbers supposed to add up somehow?