Grand total of branch measure

[pusca]

Hi all,

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.

1. **Total Collateral Value** (+) = SUMX( Facts ; Facts[Collateral value] )
2. **Total Exposure Amount** (-) = SUMX( Facts ; Facts[Exposure amount] )
3. **Total Approved Exposure Limit** (+) = SUMX( Counterparties; Counterparties[ApprovedTotalLimit] )
4. **Limit Capacity** = **[Total Approved Exposure Limit]** + **[Total Exposure Amount]**
5. **Collateral Capacity** = **[Total Collateral Value]** + **[Total Exposure Amount]** 
6. **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!

[sam.mckay]

Can you add a picture of the results in a table? I just want to see what it looks like right now, then I’ll try to replicate. Chrs

[sam.mckay]

Maybe a picture of all of the measures side by side in the table with the customer filter, that would be great. Thanks

[pusca]

Sure, I set it up in Excel as below:

The red cell is what I get in Power BI since it only takes the min value of the previous to measures totals. However, what I would like is for the total in the column at the far right to show the total sum of the values in that column.

Thanks!

[sam.mckay]

Using the below technique will get you what you need.

The key is to get a virtual table going to iterate through. This is crucial for the grand total especially.

Minimum Testing = 
SUMX( 
    SUMMARIZE( VALUES( Sales[Customer ID] ), 
        "Sales", [Total Sales],
        "Sales LY", [Sales LY] ),
            IF( [Sales] < [Sales LY], [Sales], [Sales LY] ) )

Also make sure to iterate through something from the fact table versus a lookup table. You’ll see I’ve done this with the VALUES( …ID) column.

Give this a go and let me know if any success.

[sam.mckay]

You could actually also use this formula

Minimum Testing = 
SUMX( 
    SUMMARIZE( VALUES( Sales[Customer ID] ), 
        "Sales", [Total Sales],
        "Sales LY", [Sales LY] ),
            MIN( [Sales], [Sales LY] ) )

Same results

[pusca]

So, just had a go at it again but unfortunately can not get it to work as expected. I just get the same result. The Total value I am expecting under “Credit Capacity SUMMARIZE” is 2,440, not the min of Collateral Capacity and Limit Capacity.

Here is the formula I am using in "Credit Capacity SUMMARIZE

Credit Capacity SUMMARIZE = 
    SUMX(
    SUMMARIZE(
        VALUES(Facts[CounterpartyID]);
        "CollCap";[Collateral Capacity];
        "LimCap";[Limit Capacity]);MIN([CollCap];[LimCap]))

Have tried looking at SUMMARIZECOLUMNS as well but no better luck there unfortunately.

Thank again for you help!

[sam.mckay]

Ok it looks like it will be to do with the VALUES part of the formula.

Can I see an overview of the model & relationships.

This is definitely the technique to use, it’s a matter of landing on the correct inputs.

Can you actually place the counterparty name in the table instead of the ID. Want to see if that works also.

[pusca]

I have tried referencing counterpartyID in the facts table as well as in the counterparties table. Both have the same behaviour. I tried leaving the counterparties table completely out of the measures and visuals but it gives me the same result.

I think he the model is quite simple and all seems to work well connection wize.

Counterparties[CounterpartyKey] <-> Accounts[CounterpartyKey] (this is the only multidirectional relationship in the model)
Accounts[AccountKey] -> Facts[AccountKey]

[sam.mckay]

Do you need the multi direcitonal? Is the counterparties table on the one side? If so you shouldn’t need this and could be the issue. I’m not sure but I try to avoid these as much as possible.

I’m a bit unsure why it wouldn’t be working based on what I’m looking at in the model (other than the MD potentially).

Any chance of sending me model, that all I think of right now if this doesn’t work.

[pusca]

The reason why I used the multidirectional is because I in some cases want to filter out counterparties that have a specific account type. Its not really needed in this specific case however so I removed it. Unfortunately still no success.

I tried attaching a zip file containing a dummy model but was only allowed to attach picture formats in the forum.

Added it to my onedrive instead and it should be downloadable via this link

DummyModel.zip

This is driving me crazy. Seems like it’s would be quite normal problem with a easy built in solution.

[pusca]

Wow! Think I just solved it.

This is how the syntax should be

Credit Capacity SUMMARIZE =

SUMX(
SUMMARIZE(Counterparties;
    Counterparties[CounterpartyName];
    "CreditCap" ; MIN([Collateral Capacity];[Limit Capacity]));
        [CreditCap]
)

[sam.mckay]

Nice, I’ll have a look over the next couple of days to understand why that worked but not the orginal solution I came back with. But nice work if you got it working

[sam.mckay]

Interestingly this works in your model

Credit Capacity SUMMARIZE = 
SUMX(
  SUMMARIZE( Counterparties, Counterparties[CounterpartyName],
    "CollCap", [Collateral Capacity],
    "LimCap", [Limit Capacity] ),
         MIN( [CollCap], [LimCap] ) )

So it has to do with something with the table within the SUMMARIZE function. It was reading it correctly when it was just VALUES.

It’s quite intriguing. I’m working through it to understand it much better.

[JarrettM]

Had a very similar situation today. The measure was totaling correctly for every job#, but the total was incorrect. here is a picture of the DAX calculation that worked properly. Thanks for this post!

[sam.mckay]

That’s great. Thanks for sharing.