cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrodriguez Frequent Visitor
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
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


22 REPLIES 22
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Bmsams New Member
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.

Highlighted
Super User
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)

 

 

SatyaVaitla Regular Visitor
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
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

SatyaVaitla Regular Visitor
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?

itchyeyeballs Established Member
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

 

Have a look here - http://www.powerpivotpro.com/2010/09/averagex-the-5-point-palm-exploding-fxn-technique-revisited/ 

 

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.

SatyaVaitla Regular Visitor
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.

 

Project1level2level3level4levelbudget
Power Plant    1800
Power PlantCivil   500
Power PlantCivilBuildings  300
Power PlantCivilBuildingsOffice 100
Power PlantCivilBuildingsOfficeSignature Tower100
Power PlantMechanical   900
Power PlantMechanicalBoiler  300
Power PlantMechanicalBoilerGenerator 100
Power PlantMechanicalBoilerGeneratorTurbine100
Power PlantMechanicalBoilerGeneratorChimney100
Power PlantElectrical   800
Power PlantElectricalSwitch Yard 600
Power PlantElectricalSwitch YardTransmission Line 500
Power PlantElectricalSwitch YardTransmission LinePlant Area100
Power PlantElectricalSwitch YardTransmission LineGeneration Area100
Power PlantElectricalSwitch YardTransmission LineColony Area100
Power PlantElectricalSwitch YardTransmission LineOffice Area100

 

 

 

Super User
Super User

Re: Level of Detail Calcs

Are these numbers supposed to add up somehow?