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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nmaddock
Helper I
Helper I

DAX calculation in a different context

Hi,

 

Im trying to calculate Kg's per M3 for industry types based on all the jobs we have already done and then use this value in a report in a different context. i'e' in the context of a service agreement. The problem is because the context has changed it doing the calculations against jobs of the particular agreements instead of all the jobs which is what I want.

For eaxmple if i take Cafe as an example, the weight, volume and kg's per m3 is calculations are correct.

3.jpg

 

If I use this in a different report and add on agreements I get the following:

4.jpg

 

What I want to achieve is below, how can I do this?

5.jpg

 

Please find link the sample file, any help is much appreciated.

https://www.dropbox.com/s/tfiu1tnlsessn47/Power%20BI%20Sample.pbix?dl=0

 

Nathan

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @nmaddock 

You can use ALLSELECTED over your table to achieve your goal.

KG's Per M3 = 
IF ( NOT ISBLANK ( [Total Weight] ),
    CALCULATE(
        DIVIDE([Total Weight], 
        DIVIDE([Total Volume],1000))  
        ,ALLSELECTED(Agreement)
    )
)

KGperM3.jpg

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
Super User

Hello @nmaddock 

You can use ALLSELECTED over your table to achieve your goal.

KG's Per M3 = 
IF ( NOT ISBLANK ( [Total Weight] ),
    CALCULATE(
        DIVIDE([Total Weight], 
        DIVIDE([Total Volume],1000))  
        ,ALLSELECTED(Agreement)
    )
)

KGperM3.jpg

Hi @jdbuchanan71 

 

I've updated the report but I'm getting gaps where there should be the same value, is this due to the other tables in my report?

 

Please find the link to the updated report:

https://www.dropbox.com/s/4gizjerw7ddd23k/Power%20BI%20Sample.pbix?dl=0

 

Many Thanks for your help.

 

Nathan

4.jpg

Many Thanks Nathan

Hello @nmaddock 

Because the [KG's Per M3] measure needs [Total Weight] and [Total Volume] and on those line with blanks there is no weight or volume we get blank.  What would be the basis of the calculation for those lines?  Meaning, what should it be and how did you get to that number?

The KG's Per M3 is supposed to be based on all the jobs of an industry type, for Cafe is think it was 91.15. so as long as the agreement is of type Cafe I want the 'KG's Per M3' for that industry type to be displayed.

If I select a specific sales agent this also changes the value, the Kg's per M3 is supposed to be a fixed value regardless of the other filters on the page as its needs to be based on all the jobs with a weights greater than 1 / volum / 1000 based on the industry type

That worked perfect, thank you 🙂 its easy when you know how 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.