I have stumbled upon the below problem and can’t really figure out how to take it further.
To start I have set up three base measures (1-3) and branched them further into the total of six measures laid out below.
- Total Collateral Value (+) = SUMX( Facts ; Facts[Collateral value] )
- Total Exposure Amount (-) = SUMX( Facts ; Facts[Exposure amount] )
- Total Approved Exposure Limit (+) = SUMX( Counterparties; Counterparties[ApprovedTotalLimit] )
- Limit Capacity = [Total Approved Exposure Limit] + [Total Exposure Amount]
- Collateral Capacity = [Total Collateral Value] + [Total Exposure Amount]
- Credit Capacity = MIN ( [Collateral Capacity] ; [Limit Capacity] )
Now, if I add a filter column such as CustomerName into a table and then add the above measures, they all work like a charm and calculate vast amount of data in the blink of an eye which is wonderful. But measure nr 6 only works when being filtered, ie on the rows with a customer name. The grand total of measure nr 6 is only MIN() of the grand total of measure nr 4 and 5.
This is really understandable since there is nothing filtering the grand total calculation, however I was hoping there was a way to utilize the HASONEFILTER()-function to somehow handle the grand total. Unfortfunately I can’t enter an expression built up of more than one measure into that function.
My question is therefore - is there a way to get the grand total sum of a column of measure values if the measure is expressed as either MAX() or MIN() of two other measures?
I have been trying to google my way forward regarding this problem but with no success. Which actually only leads me to think that I have completely misunderstood something really simple. Hope someone can shed some light on the problem for me or point me in the right direction.
Thanks in advance!