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
Anonymous
Not applicable

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
Super User
Super User

Hi, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

thank you!

Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@Anonymous , Try a new measure

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

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.