Forum Discussion
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
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.
24 Replies
- Greg_Deckler
Community Champion
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.
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.
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.
belmoreHelper II
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
Advocate I
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.
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. :)
- alm5084
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.
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 exampleLink to Tableau exampleI 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.
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:
- alm5084
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 DESC Business Unit Allocation Load Hhm Miles Calculation 4971121 PLNA PLNA 23 179 4971121 PLNA PLNA 179 179 4971125 GC GC MISC 84 354 4971125 GC GC MISC 354 354 4971125 PLNA PLNA 50 354 4971125 PLNA PLNA 61 354 4971125 TCAR TCAR 46 354 4971125 TCAR TCAR 76 354 Total 266.5 This is the sample data I entered into Table:
Load BOL Nbr DESC Business Unit Ship Day Load Hhm Miles Allocation 4971121 PLNA 1/1/2016 179 PLNA 4971125 GC 1/1/2016 354 GC MISC 4971125 PLNA 1/2/2016 50 PLNA 4971125 TCAR 1/3/2016 46 TCAR 4971121 PLNA 1/1/2016 23 PLNA 4971125 GC 1/1/2016 84 GC MISC 4971125 PLNA 1/2/2016 61 PLNA 4971125 TCAR 1/3/2016 76 TCAR I'm trying to control the way data is summarized in a Power BI report. My data looks something like this:
Customer Project Team Member Project Price Work Hours Cust1 Project 1 TM 1 10000 10 Cust1 Project 1 TM 2 10000 20 Cust1 Project 1 TM 3 10000 30 Cust1 Project 2 TM 1 5000 5 Cust1 Project 2 TM 2 5000 10 Cust1 Project 2 TM 3 5000 15 Cust 2 Project 4 TM 5 45000 200 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?
Customer Project Team Member Project Price Work Hours Cust 1 Total 15000 90 Project 1 Total 10000 60 Cust1 Project 1 TM 1 10000 10 Cust1 Project 1 TM 2 10000 20 Cust1 Project 1 TM 3 10000 30 Project 2 Total 5000 30 Cust1 Project 2 TM 1 5000 5 Cust1 Project 2 TM 2 5000 10 Cust1 Project 2 TM 3 5000 15 Cust 2 Total 45000 200 Project 2 Total 45000 200 Cust 2 Project 4 TM 5 45000 200 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.
