New to BI and Enterprise DNA.
One of Sam’s first best practices to follow is to use measures instead of calculated columns in order to keep the model light.
Tried that with the following but the result I get is nothing close to correct.
You are correct on the best practices. As much as possible, try to use measure instead of Cal. columns. The reasons being a measure does not get evaluated until to use it whereas a cal. col will materialize within the model. However, there are scenarios when we have no choice but to create a column.
Moving on to your issue, as per the formula, it looks like all the fields (such as quantity, unit price etc) are part of same table. If that is the case, you can first create the basic measures and then apply the mathematical logic and then use measure branching (another term for using measure within a measure).
However, the main difference will be in the total column. Sum is a column level (aggregator) function hence the its total is based on the other totals. Whereas sumx work on each row, and the total is based on each row.
See below examples:
Using the Sum the total will not match the total in rows. This is not wrong, This simply means that the mathematical logic is also applied to “Total Row”. Think of Total as a row in itself.
Using the Sumx, the total is also correct as once every row is evaluated then it is added (summed up).
I hope this will explain and help you. Incase if you still have doubts, please attach a sample file and expected result so I can dig deeper.
BTW, the formula that you used does look correct and valid. If the expected result is wrong, then it is due to the context. So providing a sample would be super helpful.
anyway possible to convert the measures/names into English.
Alternatively, you can show the expectations in a excel sheet and share that ( sample data, and sample example of what is your expectation) and I will try my best to incorporate the logic needed.
The formula is correct and is giving the right result as per the context. I have verified the information, I however would suggest doing some data cleansing as it will become easier and clearer as you make various measures.
For example, the discount column, if there is no discount, then you should replace blank/null with 0 since the discount can range anywhere from 0 to 100 percent. Small things like these will help and goes long way. I would suggest, watching @UBG by @sam.mckay.
Since all the columns that you need are already present in the same table (Buys table), you will not have to worry about any iterative functions. Aggregates will do the job.
Here is my alternate measure.
Moreover, within your key measures table, have base measures such as sum(‘Buys’[Quantity]) etc, so you can reference these measures again and perform what EDNA calls “measure branching” or nested measures.
Yes, Measures over cal col always (whenever possible), There are times when you wont have to use calculate, unless you want to change the context. Whenever there is a need for context transition, you will need to use calculate.
