Subtotaling a measure in a matrix

[Anonymous]

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:

• Sales $ = SUM('Item Ledger Entries'[Sales Amount])
• Total Sales $ = CALCULATE([Sales $], ALLSELECTED())
• Sales % of Total = DIVIDE([Sales $], [Total Sales $], 0)
• KG = SUM('Item Ledger Entries'[Kg-Quantity])
• Total Kg = CALCULATE([KG],ALLSELECTED())
• Kg % of Total = DIVIDE([KG],[Total Kg],0)
• Costs $ = SUM('Item Ledger Entries'[Cost Amount])
• GP $ = [Sales $] - [Costs $]

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:

MME2 = IF(HASONEVALUE('Item Ledger Entries'[Item No_]), [Kg % of Total] * [GP $] , CALCULATE(SUMX('Item Ledger Entries', [Kg % of Total] * [GP $] ) , ALLEXCEPT( 'Item Ledger Entries', 'Item Ledger Entries'[Item Group Code])))

Does anyone know the solution to that? Or any advice? Your help will be greatly appreciated. 

Christine

[Anonymous · Accepted 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

[Anonymous]

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

[Anonymous]

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 

[Anonymous]

I understand.

My knowledge comes from 2 sources about DAX: from here https://www.sqlbi.com/training/
and from here https://www.amazon.co.uk/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978/ref=asc_df_150930....

I know both are paid for but there's nothing in this world that's more thorough than the 2 above - they contain all the wisdom about DAX. Written by the ultimate Masters in DAX.

Best
D

[Anonymous]

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

[Anonymous]

No problem, @Anonymous. If you always stick to Best Practices of dimensional modeling and know the idiosyncracies of DAX, you'll be safe. ALLSELECTED is the most complex function in all DAX and measures that use it should never be used within iterators, NEVER, EVER. They should always be top-level measures.

Take a look at the measures I wrote. I couldn't use the measures with ALLSELECTED() under SUMX because the latter is an iterator.

Best
D

[Greg_Deckler]

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:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[Anonymous]

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

MME5 =

VAR _table = SUMMARIZE( 'Item Ledger Entries' , [Item Group Code] , "_value" , [Kg % of Total] * [GP $] )

RETURN

IF( HASONEVALUE( 'Item Ledger Entries'[Item Category Code]) , [Kg % of Total] * [GP $] , SUMX( _table , [_value] ) )

and the results are still not correct. Not sure what I am doing wrong here. 

The third link, if I read it right, the principle is the same as what I have done with MME5, to the only difference that it takes yet another layer. 

Any further advise?

Christine