how to get matrix total column to sum the way I want

[wpf_]

I have a matrix that has values for the total of each location per day.  However, FL needs it by times 2.  My measure is the following to get the value of each:

Value = IF(SELECTEDVALUE('Table'[Location])=FL,

SUMX('Table', ('Table'[PrimaryLocation]+'Table'[SecondaryLocation])*2),

SUMX('Table', 'Table'[PrimaryLocation] + 'Table'[SecondaryLocation])

)

So if Location is FL it doubles the count.  If not, then its just adding the primary and secondary count of each location to get the total count for that location.  However, since the total column is not FL either, it adds up FL's regular counts instead of the double counts.  

How can i get the total column to add up FL's double column along witht he rest of the locations (minus FL regular)?  

Is there a way to not use the total column provided by the matrix and use a custom total column?  

Is there a way to reference the total column and use that in the measure as one of the conditions?  

Thanks.

[parry2k]

@wpf_ I guess you are adding this as a measure, correct?

[wpf_]

@parry2k Yes the values are measures added in the matrix.

[amitchandak]

@wpf_ , Try like

Value = IF(MAX('Table'[Location])=FL,
SUMX('Table', ('Table'[PrimaryLocation]+'Table'[SecondaryLocation])*2),
SUMX('Table', 'Table'[PrimaryLocation] + 'Table'[SecondaryLocation])
)

[kriscoupe · Accepted solution]

Hi @wpf_,

Yes total rows are always a bit of a pain but you can work around this

Value =
SUMX(
    VALUES( 'Table'[Location] ),
    VAR PrimarySecondarySum = 
    CALCULATE( SUM( 'Table'[PrimaryLocation] ) + SUM( 'Table'[SecondaryLocation] ) )
    RETURN
    IF( 'Table'[Location] = "FL", PrimarySecondarySum * 2, PrimarySecondarySum )
)

When 'Table'[Location] is in the context it will work out the SUM of the 2 columns and if the context is FL then it will double it. When the grand total is in the context VALUES will return all the 'Table'[Location]'s available in the context and work them out seperately and add them up.

In order to treat the total column seperately you can use the HASONEVALUE formula to check but this is not flexible and if your visualisation changes then you'd likely need to rewrite your measures to accomodate.

Hope it helps.

Kris

[wpf_]

@kriscoupe ,

You have made my day! thanks.  I just couldnt' figure out how when total is in the context, it gets the doubled column value and not the regular value.  So is the trick in the CALCULATE function, or just the way the expression was structured in the SUMX function, where you have the if condition after the variable declaration? 

And where you have a variable in the Sumx function, that kind of throws me off, in addition to the Return statement.  I didnt know you can actually create and assign to a variable within that sumx context, AND return.  The microsoft docs does not really get into detail the possibilities of functions.  

[kriscoupe]

@wpf_ 

Ah hah, glad to help 😀!

So the trick is in the combination of the two together (SUMX/CALCULATE).

• The VALUES statement first created a distinct list of all Locations in the current context. So when you're looking at the numbers for each location then that location is the only one in the VALUES statement but in the total row it is all the locations.
• Then wrapping a CALCULATE around the SUM statements ensures context transition occurs to make sure only the values for that Location code are summed from the table. Have you ever tried writing a SUM in a calculated column and noticed it returns the SUM of the entire column in each cell? Wrap this in a CALCULATE and see what happens, this is called context transition.
• Then since we have a SUMX we are iterating over the VALUES of each Location doing the calculation, when we come across "FL" we double the number using the IF. Then we sum up all the individual values. So this works for each location seperately and the grand total.

The VAR syntax is just so I didn't have to write the code twice in the IF statement in this regard, lending to cleaner code. You should definitely get your head around this syntax though as it can provide MUCH faster code in a lot of circumstances but worth getting your head around how they work first. Here's an article to start but I can't recommend more to get your hands on The Definitive Guide to DAX (2nd Edition) provided by MS (and written by the guys who run this blog)

https://www.sqlbi.com/articles/variables-in-dax/

All the best,

Kris

[wpf_]

@kriscoupe ,

Thanks for the explanation.  Yes you are correct I will have to understand the concept more in order to use them effectively.  I notice sometimes the functions aren't really what it might suggest.  Thanks for the article and book recommendation I will surely be taking a look at them.  Appreciate your help!