cancel
Showing results for
Did you mean:
Helper III

## Why is column showing different calculation than a measure?

I created 4 Total Columns as you can see in the pictures. The first three are measures and are caluclated using the formula shown in the formula bar:

Total Purchase Amount BK = CALCULATE(SUM(Purchase Amount), FILTER(Asset Class ="Bankruptcy"))

Then I created a cloumn with the following formula:

Total Purchase Amount = SUM(Purchase Amount)

As you can see I'm recieving a different total in the 'Total Purchase Amount' column than I am for the measure. The Total Purchase Amount is segmented by the asset class so why am I recieving two different values as highlighted in picture.

22 REPLIES 22
Super User

THere must be more classes than you have measures for.

Create a new matrix. Put the Total Purchase Amount measure in the values, the Asset Class Segment in rows, and the Asset class field in the column. That should show you everything, and just one measure is necessary.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper III

There are more classes that aren't on the table but I'm filtering by the three asset class segments that are displayed on the picture I attached. So why would i matter if there are more asset class if I'm filtering out all other asset classes?

Kudo Kingpin

Total Purchase Amount =
CALCULATE ( SUM ( PurchaseAmount ) )

*Aggregation functions like SUM *always* reference the filter context, so they always return the whole column, unless CALCULATE() is used.

Helper III

I tried to go about it a different way, so now both the Total Purchase Amount BK (measure) and Total Purchase Amount (column) have the same exact formula but are giving me different answers. Why is this?

Total Purchase Amount (Column) = CALCULATE(SUM(Purchase Amount, FILTER(Asset_Class_Segment__c="Bankruptcy"))

Total Purchase Amount BK (Measure) = CALCULATE(SUM(Purchase Amount), FILTER(Asset_Class_Segment__c="Bankruptcy"))

Why would I get different answers?

Solution Sage

Hi @mikep7521,

A more comprehensive answer can be found here, but in brief, calculated columns are evaluated once for each row in the table, while measures are evaluated when a visual is rendered. For practical use:

1. If you intend to apply any aggregation to a value (ie. sum, average etc) that shows a result, then stick to a Measure (in my even shorter version, if you're measuring something, use a measure).

2. If you intend to use the value for formatting purposes (ie. slicers, row/column category, bucketing, sort order), then add a Column.

In your case, since you're looking for sums, you'll want to stick with a measure.

Helper III

So there is no way to create a column that returns the values i have in the measures?

Super User

Yes, but not the way you are doing it. Here is what I would do.

• Just have one measure - your total purchases.
• Put your existing rows as rows in a new matrix.
• Put the total measure in the value.
• Put the asset classes in the column.
• Now, add a slicer and put the asset classes there too. Select the 2-3 asset classes you want to see.

Now all columns and the total will honor the filter context and row context of the report. And, there is just one measure.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Solution Sage

Correct. You can use calculated columns to return a value like a SUM or AVERAGE, but they exist at a row level, and are static.

Is there an issue with using measures?

Helper III

Solution Sage

Hi @mikep7521,

Based on what you wrote, you want the Sum of WA for bankruptcy only, correct? In that case, you'll need to filter the Sum of WA column. This would make sense for something singular like a card, but otherwise just use a slicer.

If you just do "Sum of WA / BK" you're getting 1.3 billion / 26 million.

Helper III

how would I accomplish adding the filter within the equation to have just BK only so I'm using the 36million instead of the 1.3 billion. I'm using this formula (below) and receiving 16.42 as an answer.

BK Multiple = CALCULATE(SUMX(Sum of WA/Total Purchase Amount BK),FILTER(Asset_Class_Segment__c="Bankruptcy"))

Super User

@mikep7521 wrote:

how would I accomplish adding the filter within the equation to have just BK only so I'm using the 36million instead of the 1.3 billion. I'm using this formula (below) and receiving 16.42 as an answer.

BK Multiple = CALCULATE(SUMX(Sum of WA/Total Purchase Amount BK),FILTER(Asset_Class_Segment__c="Bankruptcy"))

If you want to filter the data, you have to do that in Power Query. DAX measures only filter data for calculation. It still pulled in the 1.3 billion rows. Power Query filters the data that goes into the DAX calculation model.

ANd I'd still do it the way I specified above, but it is ok to "pre-filter" the data coming into DAX via Power Query. But the logic of my single measure, matrix, and slicer would still hold. It would be dynamic. If you later go back into Power Query and change the filter that lets more/less data through, the refresh would be reflected. No editing or creating new measures for the new columns.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Kudo Kingpin

"Evaluation contexts is one of the hardest things to master in DAX. It is not uncommon to think you know how evaluation contexts works only to find that you are completely stumped by a formula that is not doing what you expect. In this session we will clearly explain the concepts of row context, filter context and context transition using on screen examples that anyone can understand."

Super User

@freder1ck wrote:

"Evaluation contexts is one of the hardest things to master in DAX. It is not uncommon to think you know how evaluation contexts works only to find that you are completely stumped by a formula that is not doing what you expect. In this session we will clearly explain the concepts of row context, filter context and context transition using on screen examples that anyone can understand."

Cannot be overemphasized...

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Microsoft

Hi @edhans,

Can you share a sample of your data? That would easier for us to discuss more. To be honest, your issue could be solved in a simple way.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

I shared a screen shot of the data I was working with earlier in this forum. I'd be very interested to hear your thoughts of accomplishing this in a more simple way.

Microsoft

Hi @mikep7521,

Your snapshot shows the result rather than the raw data. We need the data and the model structure. You can mask the sensitive data first.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@mikep7521 wrote:

I shared a screen shot of the data I was working with earlier in this forum. I'd be very interested to hear your thoughts of accomplishing this in a more simple way.

@mikep7521 - see this data. I didn't know what "WA" was in your post. I assumed Washington state and made some data that is by asset class and state. I just have one sum measure, and it works everywhere for totals. If you need specific areas called out, you'd need to explain better, and as others have suggested, post sample data with expected result. I cannot take it any further without having a better understanding of how your data model looks.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Solution Sage

Hi @mikep7521,

Just put some sample data (anonymized) into an Excel workbook and share it with a download link from OneDrive/Dropbox/Google Drive, etc. Then, using that data, tell us the expected result.

Solution Sage

You want to just filter Sum of WA - in your example, you're doing the division first, and then filtering the whole result by bankruptcy.