Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrodriguez
Regular 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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

24 REPLIES 24
Sripriya
Frequent Visitor

This is not a suggested approach, but you can achieve the FIXED LOD concept in tableau using Group By function in Power BI query editor. Create a duplicate query with the fixed data that you want and later you can merge the data and use it for other visulaizations. This worked for me. Hope this works for you too. 🙂

 

 

 

@Sripriya Your solution would technically work, but only with smaller amounts of data. If your grouped query result is several million rows, and your ungrouped query result is 10s of millions of rows, the query might hang forever trying to run. I've had this happen with complicated data heavy queries. The more appropriate solution for calculating a measure at a different level of granularity is to write a level of detail measure in DAX. It can be confusing at first, but once you understand the pattern you can reproduce it easily.

 

If you need a grouped table result - the only reason you would is if you need to use the columns as a categorical axis in a visual - you can create the table using a DAX statement as well. This is the data modeling version of a database view, whereas duplicating the query with a grouped statement would be the database equivalent of actually duplicating the data in a second table. You don't ever want to actually duplicate the data. It not only increases the size and complexity of your Power BI file, it also introduces an additional place for user error when you are updating the data later.  

Hi @alm5084, I'm looking for a solution as you describe in your second paragraph -- using a DAX summary table as a categorical axis in a visual. 

I'd like to be able to assign a customer to a group based on the result of an aggregation, but I'm running into order of operations issues. My data source also has row-level security and I've not been able to find a great way in DAX to have the RLS rule apply, run the aggregation and then output this result into a column that I can use as a categorical axis. 

Is there any way to avoid pre-computing these values in PowerQuery or in a database view? 

When I've even tried to create a summary table in DAX and related it back to my clients table, I'm unable to get away from circular dependency issues, which has also led me down the PowerQuery summary route. 

I've seen a lot of Tableau LOD substitutions in PowerBI, but all of the {FIXED} examples lack the key feature in Tableau -- that the results of these calculations are available at the row-level but also respond to both data source and context filters (which can include either RLS rules or filter actions taken by users). 
Thanks for any help you can provide!

Anonymous
Not applicable

Need to convert the following Tableau Level of Detail Calc to DAX:

{ FIXED [User], [Date]: AVG([Cost])/COUNT([Actions])}.

 

And need to see results by Customer.  I've tried AllExcept without success.

 
Ave cost per date per action =
DIVIDE(
CALCULATE(
AVERAGEX(Sheet1,Sheet1[cost]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[user])),
CALCULATE(
COUNTAX(Sheet1,Sheet1[action]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[user])))
 
Power BI matches the Tableau LoD when viewed in a table with "user" and "date", but when I put "customer" in the table, instead of "user" and "date", the Power BI table results no longer match the Tableau LoD resutls.
 
Link to Power BI example
 
Link to Tableau example
 
I think I just need to figure out how to sum or total up the results at the Customer level.  But I don't know how.

Hi! Did you ever figure this out? It looks similar to an issue I'm having and was wondering if you had a solution.

jburia
Regular Visitor

Hello, I'm having a similar issue migrating from Tableau. My dataset has several levels within one flattened table. As you can see in the screenshot, the Miles for each Bill of Lading are duplicated across each of the Business Units.

 

In Tableau, I can calculate an Average Miles per Load using the Average of the following LOD Function to calculate Average Miles per Load BOL Number. How can I use DAX to perform the same calculation?

 

{Fixed [Load BOL Nbr DESC],[Ship Day]: Max([Load Hhm  Miles])}

 

Load 4971121 -               171 Miles

Load 4971125 -               354 Miles

Average Miles per Load - (354+179)/Distinct BOL Count (2) = 266.5

Tableau Screenshot:

 

tableau.png

 

 

Hi jburia,

 

I'm not sure if you ever figured this out, but it is doable in Dax. It's funny because I came to this board looking for help to translate Dax into Tableau calculations. You could try this calculation (I added the data as a table named Table):

 

Calculation =
IF(DISTINCTCOUNT('Table'[Allocation])=1,
        CALCULATE(
             MAXX('Table'[Load Hhm Miles]),
            ALLEXCEPT('Table','Table'[Load BOL Nbr DESC])),
AVERAGEX(
       SUMMARIZE('Table','Table'[Load BOL Nbr DESC],"MaxMiles",MAX('Table'[Load Hhm Miles])),[MaxMiles])
)

 

The big difference between Power BI and Tableau in this case is that you can't calculate the total differently from the rest of the measure in the visual. In Tableau, you can add your [Load Miles] calculation to the table, and then add a total and tell it to average that total. In Power BI, the total row calculates with exactly the same measure formula as the rest of the rows in the visual. So, to get a different calculation for the total row we use the IF statement to test whether there is more than one [Allocation] value, and return a different calculation based on the result of the logical test.

 

If there is only one value for [Allocation] then it calculates the max [Load Hhm Miles] per [Load BOL Nbr DESC]. The CALCULATE function is the level of detail expression for Power BI, like your Fixed function in Tableau. The magic here is the filter for the CALCULATE, which is the ALLEXCEPT. This says to ignore all context except the [Load BOL Nbr DESC].

 

If there is more than one value for [Allocation] as is the case for the Total row, then it uses the AVERAGEX calculation. AVERAGEX takes as its arguments a table and an expression to average over. So we create a summarized table to pass to AVERAGEX with SUMMARIZE, where we calculate the max[Load Hhm Miles] per [Load BOL Nbr DESC]. AVERAGEX then averages these max[Load Hhm Miles], and returns the proper result in the total row. These are my results:

 

Load BOL Nbr DESCBusiness UnitAllocationLoad Hhm MilesCalculation
4971121PLNAPLNA23179
4971121PLNAPLNA179179
4971125GCGC MISC84354
4971125GCGC MISC354354
4971125PLNAPLNA50354
4971125PLNAPLNA61354
4971125TCARTCAR46354
4971125TCARTCAR76354
Total   266.5

 

This is the sample data I entered into Table:

 

Load BOL Nbr DESCBusiness UnitShip DayLoad Hhm MilesAllocation
4971121PLNA1/1/2016179PLNA
4971125GC1/1/2016354GC MISC
4971125PLNA1/2/201650PLNA
4971125TCAR1/3/201646TCAR
4971121PLNA1/1/201623PLNA
4971125GC1/1/201684GC MISC
4971125PLNA1/2/201661PLNA
4971125TCAR1/3/201676TCAR

I'm trying to control the way data is summarized in a Power BI report. My data looks something like this:

CustomerProjectTeam MemberProject PriceWork Hours
Cust1Project 1TM 11000010
Cust1Project 1TM 21000020
Cust1Project 1TM 31000030
Cust1Project 2TM 150005
Cust1Project 2TM 2500010
Cust1Project 2TM 3500015
Cust 2Project 4TM 545000200

 

In the report, I have summarization at the customer and project levels, summing the project price and work hours values. The project prices, as you'd imagine, shouldn't be added together until the summary at the customer level. The work hours should be summed at both the project and customer level so that I get a report that looks something like this?

 

CustomerProjectTeam MemberProject PriceWork Hours
Cust 1 Total  1500090
 Project 1 Total 1000060
Cust1Project 1TM 11000010
Cust1Project 1TM 21000020
Cust1Project 1TM 31000030
 Project 2 Total 500030
Cust1Project 2TM 150005
Cust1Project 2TM 2500010
Cust1Project 2TM 3500015
Cust 2 Total  45000200
 Project 2 Total 45000200
Cust 2Project 4TM 545000200

 

I gather that there's a way to do this with DAX by defining the measures properly but I could use some help figuring out what that is.

To use the data as you have it set up, you should be able to use the Work Hours column directly with Sum as the aggregation to get Work Hours to total this way.

 

You can simply use the sumx function here, with the table argument being a summarize function that groups the data by Project and Project Price. I added your data as a table name 'Table'.

 

Project Price Measure = SUMX(SUMMARIZE('Table','Table'[Project],'Table'[Project Price]),[Project Price])

 

If there are millions of rows and the connection to the data is live, you might see a slow calculation using the sumx over the whole table.

 

jnedro6
New Member

Were you able to figure out how to do this?  I, too, am a Tableau user and I am very familiar with LOD calcs.  I have just started using Power BI but I am stuck on a project right now because I need to be able to perform a similar calc to an LOD.

I was not able to get an answer........it must be very painful to recreate (if at all) using DAX. Please let me know if you we able to learn anything.

 

thank you

Just want to add that I am working on some LOD-type calculations and I am having a lot of trouble reproducing accurate results, even when following the steps above.

 

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Sean
Community Champion
Community Champion

@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)

 

 

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

Sean
Community Champion
Community Champion

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

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?

@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.

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.