Forum Discussion
Super UserLOD (tableau) into Power BI?
Hello everyone,
I'm working on a migration from tableau to Power BI, and I'm stucked on a measure for a few hours
This is the measure I have in tableau:
SUM({INCLUDE [Security] : SUM([Amount])^2}) / SUM([Amount])^2
I'm stuck in the first part with the ², without it I'm able to reach the results I need via SUMX and Values(Security) but once I add the ^2, it returns me weird values
I also tried with ALL and ALLEXECPT but I'm not able to return the correct value
Any idea of how could I achieve that?
Cookistador So, in Power BI that is going to square that number. If you are trying to do something in regards to LOD in Tableau that generally falls under the "measure totals" area of things. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8If that is not the issue, please post additional information. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
3 Replies
- Greg_Deckler
Community Champion
Cookistador So, in Power BI that is going to square that number. If you are trying to do something in regards to LOD in Tableau that generally falls under the "measure totals" area of things. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8If that is not the issue, please post additional information. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.- Cookistador
Wonderful, I was able to fix the issue with the ressource you shared
Thank you to help to save me from madeness 🙂
Hi Cookistador ,
1. Step 1: Calculate the Squared Sum of Amount for Each Security
- First, we need to calculate the squared sum of the 'Amount' for each 'Security'. This can be achieved using the 'SUMX' and 'VALUES' functions in DAX.SecurityAmountSquared = SUMX(VALUES(TableName[Security]), CALCULATE(SUM(TableName[Amount])^2))2. Step 2: Calculate the Total Squared Sum of Amount Across All Securities
- Next, calculate the total squared sum of 'Amount' across all securities. This is straightforward with the 'SUM' function.TotalAmountSquared = SUM(TableName[Amount])^23. Step 3: Combine the Calculations
- Finally, we combine the two calculations to achieve the desired result. Since 'SecurityAmountSquared' is already aggregated at the desired level, we can directly use it in the division.FinalMeasure = DIVIDE([SecurityAmountSquared], [TotalAmountSquared])Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
