Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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!
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.
Hi! Did you ever figure this out? It looks similar to an issue I'm having and was wondering if you had a solution.
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:
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.
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.
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.
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.
@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
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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |