How to sum a column in the table view as opposed to calculating the measure

[alicia_pbi]

I have a table view that has a variety of measures calculating different data points.  One data point is calculating the over usage of a specific data point.  The measure is the actual usage - the expected usage.  If the actual usage is less than the expected usage we want to represent a 0 otherwise we will have the sum of the over usage.  This measure is working exactly as expected within the table.  However the sum row is calculating the measure based on the total of the actual usage and the total of the expected usage.  I would like to calculate the total sum of the over usage without excluding the under used items from my table.  

Due to the complexity of the calculations, this is not something I can build into the query editor so I need to use measures.

Is there a formula that tells PBI to sum a column as opposed to running the measure for the individuals rows within the column?

Thanks in advance.

[jdbuchanan71 · Accepted solution]

Hello @alicia_pbi 

It sounds like you are looking for SUMX.

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

Basically, you feed it in a table (it can be a summary table) and the calculation you want done on every row of the table and once it is done it sums the amounts.  So your measure might look something like 

Over Usage Correct Total =
SUMX (
    VALUES ( YourTable[Customers] ),
    MAX ( 0, [Actual Usage] - [Expected Usage] )
)

For every customer, if the [Expected Usage] > [Actual Usage], that will return a negative and the MAX ( 0 .. ) will return 0, otherwise return the difference.  Once all calced return the sum.

[alicia_pbi]

@jdbuchanan71That worked perfectly thank you for the reply!

[alicia_pbi]

@jdbuchanan71  Follow up question - it seems that this is not summing correctly if I have multiple criteria such as Continent, Country, Contract Number.  It is summing correctly if I look at just a single data point of the mentioned items.  Is there a way to use a multiple VALUES formula?  In the below photo the pivot table (blue header) is representing the data exported from Power BI.  The white header Sales column is the total per country based on a tablular view in my data model.

[jdbuchanan71]

@alicia_pbi 

Are you able to share your .pbix file?  It is difficult to know where the problem is coming from without seeing the strucure of the model.

[alicia_pbi]

@jdbuchanan71Unfortunately I cannot share the .pbix file as it has business specific details.  Let me try to detail out the formulas as I think I may understand the issue which perhaps you can help with. 

In order to calculate the Cost, I am using the below formula.  I believe the issue I am having is that the Rate is currently an AVERAGE which on the subtotals and totals is causing an an average calculation as opposed to summing the cost.  I chose to do this because the query with the rate is a flat table with a single line per country and the respective rate.  I couldn't find a better measure to multiply a measure by a unique value i.e. rate in the table.  I understand in the Query Editor I could merge the data but that would mean I need to create another query to group/sum the usage and merge with the rate query.

My question: Is there a better way to multiple a measure by a singular value that is in the data model?

COST = ((actual usage + forecasted usage) - hours purchased) * rate

SUMX = SUMX(VALUES('Source'[Contract]),MAX(0,[Cost]))

I really appreciate the help!

[lc_finance]

Hi @alicia_pbi ,

You could look into SUMX to calculate the right measure for the total. An easier solution, however, might be to create a calculated column instead of a calculated measure.

A calculated column applies your formulas to each row, then the total is the sum of all rows. The total is not equal to the formula.

You can create a calculated column going to Modeling -> New Column.

Let me know if this works for you,

LC

www.finance-bi.com

[alicia_pbi]

@lc_financeThanks for your reply.  Unfortunately my backend data is not a summary but is an accumulative list of details.  When I tried to create a calculated column other data such as the term of the contract and the % of the contract consumed was aggregated in a way I was struggling with.