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.
I could do with some help here to finish a formula.
One single table: Item ledger entries. Items belong to Item Categories. We want to calculate Sales, Sales % of Total, Costs, GP, Kgs, Kgs % of Total and what we call "Margin Mix Efficiency". It is this last one which causes headaches because I need to apply the calculation [Kg % of Total] * [GP $] at the item row level BUT the sum of these results at the grouping level (Item Category).
Here is a visual
The measures used are as follows:
The Margin Mix Efficiency should be [Kg % of Total] * [GP $] but at the grouping level (the category) it should do the sum of the values within this group.
MME1 to MME4 are failed tentatives. I think I got the start right but struggle with the sum. Here is the closest I got to the result:
Solved! Go to Solution.
Hi.
You should not have "one single table" in your model. It's WRONG. For many reasons, too many to talk about here. What you should do is create a star schema - a fact table (with all columns hidden, only measures exposed) connected to dimensions through which you'll be slicing. If you have only one table in your design, you'll risk the creation of numbers you won't be able to understand and which will be plain wrong (most of the time you'll not even be able to spot it). If you don't believe me, please read this article: https://www.sqlbi.com/articles/understanding-dax-auto-exist/.
Once you have the correct design. You can then write correct measures.
In your case, you could try to do this (but with the correct design as mentioned):
// Assumptions:
// 'Items' is a DIMENSION and has an attribute 'Item Category.'
// It's connected to the fact table 'Item Ledger Entries.'
// in a 1:* fashion.
// Be aware that ALLSELECTED is a very dangerous
// function and using a measure that depends on
// it inside another measure that does iteration,
// is a SURE way to fail, which is something
// you have already witnessed. If you want to know
// how to properly use ALLSELECTED, you have to
// read the article about it by Marco Russo and
// Alberto Ferrari on www.sqlbi.com
[Your Measure] =
var __totalKg = [Total Kg]
var __result =
sumx(
// iterates all the individual items
// in the current context
values( Items[Item] ),
// first, Kg % of Total must be calculated
// you cannot use the measure here because
// it uses ALLSELECTED internally
var _kg = divide( [KG], __totalKg, 0 )
// second, GP $ must be calculated
// you can use the measure because it does
// not depend on ALLSELECTED
var _gp = [GP $]
return
_kg * _gp
)
return
__result
A hint: Please do not use any function in your code whose exact behaviour you don't know. Otherwise, you'll be creating wrong numbers before you know it.
By the way, here's an alternative formulation of the above (might be faster):
[Your Measure] =
var __totalKg = [Total Kg]
var __numerator =
sumx(
values( Items[Item] ),
[KG] * [GP $]
)
var __result = divide( __numerator, __totalKg )
return
__result
Best
D
Hi.
You should not have "one single table" in your model. It's WRONG. For many reasons, too many to talk about here. What you should do is create a star schema - a fact table (with all columns hidden, only measures exposed) connected to dimensions through which you'll be slicing. If you have only one table in your design, you'll risk the creation of numbers you won't be able to understand and which will be plain wrong (most of the time you'll not even be able to spot it). If you don't believe me, please read this article: https://www.sqlbi.com/articles/understanding-dax-auto-exist/.
Once you have the correct design. You can then write correct measures.
In your case, you could try to do this (but with the correct design as mentioned):
// Assumptions:
// 'Items' is a DIMENSION and has an attribute 'Item Category.'
// It's connected to the fact table 'Item Ledger Entries.'
// in a 1:* fashion.
// Be aware that ALLSELECTED is a very dangerous
// function and using a measure that depends on
// it inside another measure that does iteration,
// is a SURE way to fail, which is something
// you have already witnessed. If you want to know
// how to properly use ALLSELECTED, you have to
// read the article about it by Marco Russo and
// Alberto Ferrari on www.sqlbi.com
[Your Measure] =
var __totalKg = [Total Kg]
var __result =
sumx(
// iterates all the individual items
// in the current context
values( Items[Item] ),
// first, Kg % of Total must be calculated
// you cannot use the measure here because
// it uses ALLSELECTED internally
var _kg = divide( [KG], __totalKg, 0 )
// second, GP $ must be calculated
// you can use the measure because it does
// not depend on ALLSELECTED
var _gp = [GP $]
return
_kg * _gp
)
return
__result
A hint: Please do not use any function in your code whose exact behaviour you don't know. Otherwise, you'll be creating wrong numbers before you know it.
By the way, here's an alternative formulation of the above (might be faster):
[Your Measure] =
var __totalKg = [Total Kg]
var __numerator =
sumx(
values( Items[Item] ),
[KG] * [GP $]
)
var __result = divide( __numerator, __totalKg )
return
__result
Best
D
Thank you Darlove. I shall mend my model straight away and try your solution.
I have so far always used the star schema (see example below, just one of them, some are far more complex), so why not for this report ... perhaps I thought I could get away with it (lesson learned), the initial request did in my opinion not require it (lesson learned here too). I check results as I go along with each measure, and on this one it went along smoothly until I hit that last measure.
When it comes down to measures and functions, I am fully conscious to not know each of them and I am still somewhere in the middle of the "learning curve" which I think Rob Collie mentioned in one of his books.
Thank you for your help. I shall now fix this.
Christine
Hi Darlove.
Thanks for having put me on the right track. I mended the model - linking to the Items tbl - and applied your formula and it works perfectly. For arguments sake, I also tried it on the file with the single table, and it worked there too. Not tempting the devil here, I do fully agree that using the star schema is a must and in the future I will NOT forsake this again.
By analysing your formula, I realise that my issue still lay with the "context" - the VALUES(Items[Item No_]). I'm struggling with the likes of VALUES, SUMMARIZE, CALCULATETABLE etc .. which will be the topic of my next study period and a deep dive into our masters books 🙂
I really appreciate you putting me on the right track.
Christine
I own the Ferrari/Russo one, along with many others. Their Udemy courses were also of great help.
There was a time when I could continuously spend time on PBI and DAX and I was well away on the learning curve, this has unfortunately become more sporadic due to other needs of my resource within the business and as the famous adage goes "practice it or lose it". It is so frustrating to know one's weaknesses and not having time or opportunity to remedy it. Then come the requests like the one above, and you are stuck. Perhaps time to talk to the boss 😉
What is great, is that there are always people like you out there, to help us on our path, and for that I am thankful.
Christine
Either this: 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
Or you maybe want this:
Thank Greg for the prompt answer.
Your first link is an example using comparison operator, which is not really my case, but I gave it a go and was stuck at the last "else", not really knowing what to put there ...
I then went to the second link. The measure is
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 |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |