cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SatyaVaitla Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

Thanks for prompt response and the answer is No

 

When fully distributed,each level will be the sum of the lower level for every hierarchy. But most of the times, we keep always some cushion at every level for future distribution. so the numbers will never add up to the higher level. Only during end of the project the lower level may add up to the next higher level.

 

But that is something which can happen but should not be assumed. The drill down should display the related budget for that level only with out considering the lower level.

 

The real complexity comes when i add one more measure - my actual costs. Because actual costs will have many line items for every budget line, there will be challenge when we need to show budget vs actual in drill down graph. Actual costs also can happen at every level of hierarchy. For example my costs can be at project level or at 1level node or 2nd level node or at any level.

 

I hope my requirement is clear now and look forward for power BI solution.

 

Regards

 

Satya

itchyeyeballs Established Member
Established Member

Re: Level of Detail Calcs

Dealing with information at different granularites will require a specific data model, have a look here - http://www.cimaglobal.com/en-gb/Thought-leadership/Newsletters/Insight-e-magazine/Insight-2012/Insig...

 

Would be useful to see how your data mosel is set up in order to address your original query, can you post a screenshot?

belmore Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

Satya,

 

I'm having the same challenge, "how to do a Level of Detail (LOD-Tableau) calculation within Power BI?"

 

Were you able to understand how to go about doing this in DAX?

 

Thank you

 

Brad

jnedro6 Occasional Visitor
Occasional Visitor

Re: Level of Detail Calcs

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.

belmore Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

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

ldacey Frequent Visitor
Frequent Visitor

Re: Level of Detail Calcs

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.

 

jburia Visitor
Visitor

Re: Level of Detail Calcs

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

alm5084 Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

 

 

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
StevenHB Frequent Visitor
Frequent Visitor

Re: Level of Detail Calcs

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.

alm5084 Regular Visitor
Regular Visitor

Re: Level of Detail Calcs

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.