Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikep7521
Helper III
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.Capture BPI 2.PNG

22 REPLIES 22
edhans
Super User
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 I answer your question? Mark my post as a solution!
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

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?

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.

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?

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.

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

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 I answer your question? Mark my post as a solution!
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

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?

 

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.

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"))


@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 I answer your question? Mark my post as a solution!
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

At the Business apps summit, Matt Allington addressed this specific topic:
https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-119

"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."

 


@freder1ck wrote:

At the Business apps summit, Matt Allington addressed this specific topic:
https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-119

"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 I answer your question? Mark my post as a solution!
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

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.

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.

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.


@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 I answer your question? Mark my post as a solution!
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

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.