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.

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,263)