cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azat_Aliaskarov
Helper I
Helper I

Grand total % of mass by category

Hi,

I have 3 tables and I've made relationships in file

address - contains mass and code (in pic it is 68720, 68748, 68754, 66076, 67800)

manifestaddress - contains keys to connect address and manifest tables

manifest - contains code (in pic it is 2308), date_mnf and transportation_cost


Code in manifest  is package consolidation, (ex. mail truck going from Settle to LA)
Code in address is code of package the same package can appear in the different package consolidation (ex. packge can go from Settle to LA then from LA to Austin, so it will have 2 codes in manifest)


I want to calculate grand total of mass by every code within 01/03/21 - 31/03/21 using dax formula, to use values in further calculations.
The outcome have to be as in the last column in pic but by codes
I choosed dates from 01/03/21 - 31/03/21
and choosed only code  = 2308. 

Azat_Aliaskarov_0-1618807237037.png

But if I choose all manifest codes then I should see something like this

Azat_Aliaskarov_0-1618815357784.png

 

@Ashish_Mathur 

@Greg_Deckler 

@Jihwan_Kim 
@marcorusso 

@AlB 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @Azat_Aliaskarov 

I have changed the filter direction like the below picture and wrote measures like below.

The link to the pbix file is down below.

 

Picture2.png

 

Mass Total =
CALCULATE (
SUM ( 'address'[mass] ),
CROSSFILTER ( manifestaddress[address], 'address'[address code], BOTH )
)

 

Percent GT Sum of Mass mnf code =
DIVIDE ( [Mass Total], CALCULATE ( [Mass Total], ALL ( 'address' ) ) )

 

 

https://www.dropbox.com/s/bqpan2i92jd3u2o/Test.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

5 REPLIES 5
Azat_Aliaskarov
Helper I
Helper I

Thank you, It did help!!!
Can you breafly explain why you changed relationship this way?
and how this formulas work?
It will help me better understand Power BI

Thanks in advance!

Hi, Thank you for your feedback.

In my case, I prefer to have waterfall-like-data-model. For instance, dimension tables on the top and fact tables on the bottom. Then,  make the filter direction flow from top to bottom. In this way, it makes me easy to understand the model and the business.

Furthermore, in this case, because the measurement has to be done in the dimension table, the filter direction has to be made the opposite way. So, I used crossfilter function.

In most cases, in order to create easy measures, the bi-directional relationship is created. In this way, it is easy to write measures, but it is not easy to understand the business, and even sometimes it creates unexpected results.

This is how I normally create a data model. I cannot be 100% correct, but I try to follow the basics.

I hope I explained well.

Thank you.

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

thank you!

Jihwan_Kim
Community Champion
Community Champion

Hi, @Azat_Aliaskarov 

I have changed the filter direction like the below picture and wrote measures like below.

The link to the pbix file is down below.

 

Picture2.png

 

Mass Total =
CALCULATE (
SUM ( 'address'[mass] ),
CROSSFILTER ( manifestaddress[address], 'address'[address code], BOTH )
)

 

Percent GT Sum of Mass mnf code =
DIVIDE ( [Mass Total], CALCULATE ( [Mass Total], ALL ( 'address' ) ) )

 

 

https://www.dropbox.com/s/bqpan2i92jd3u2o/Test.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

amitchandak
Super User IV
Super User IV

@Azat_Aliaskarov , Try a new measure

code mnf %= divide(sum(Table[Maxx]), calculate(sum([Mass]), filter(allselected(Table), Table[code mnf] = max( Table[code mnf]))))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors