cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikep7521 Member
Member

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.Capture BPI 2.PNG

22 REPLIES 22
edhans Super Contributor
Super Contributor

Re: Why is column showing different calculation than a measure?

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. 

mikep7521 Member
Member

Re: Why is column showing different calculation than a measure?

I'm confused, can you elaborate how to make asset class segment a row as well as a column?

mikep7521 Member
Member

Re: Why is column showing different calculation than a measure?

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?

freder1ck Member
Member

Re: Why is column showing different calculation than a measure?

For your column, try
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.

mikep7521 Member
Member

Re: Why is column showing different calculation than a measure?

I just made that change by using the Calculate function and the vlues in the Total Purchase Amount column did not change.

Highlighted
mikep7521 Member
Member

Re: Why is column showing different calculation than a measure?

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?

RMDNA Senior Member
Senior Member

Re: Why is column showing different calculation than a measure?

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.

mikep7521 Member
Member

Re: Why is column showing different calculation than a measure?

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

RMDNA Senior Member
Senior Member

Re: Why is column showing different calculation than a measure?

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?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)