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

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

 
RMDNA Senior Member
Senior Member

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

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.

edhans Super Contributor
Super Contributor

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

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.

mikep7521 Member
Member

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

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

RMDNA Senior Member
Senior Member

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

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.

edhans Super Contributor
Super Contributor

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


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

freder1ck Member
Member

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

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

 

edhans Super Contributor
Super Contributor

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


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

Microsoft v-jiascu-msft
Microsoft

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

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.
mikep7521 Member
Member

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

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.

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,248)