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

Calculate sum for filter related rows

hey @all,

 

its time for another question and chance to help me 😄

 

I've got following issue. I have 2 tables (without relationship, because it would be a m:n relationship).

 

table 1:

regionatc5atc7amountpricevalue
germanyA01ABA01AB011020200
usaA01ABA01AB0151575
japanA01ABA01AB012010200
germanyA01CCA01CC055525
usaA01CCA01CC057642
usaA01XXA01XX031010100
germanyA02DDA02DD015525
usaB05AAB05AA401010100
usaB05AAB05AA50111

 

table 2

 

atc7atc5icd
A01AB01A01ABE11
A01AB01A01ABE13
A01AB07A01ABE13
A01CC05A01CCE11
A01CC05A01CCG22
A01XX03A01XXG22
A01XX03A01XXG40
A02DD01A02DDE11

 

Use-Case:

 

I'd like to select A01AB in a slicer for table1[atc5]. This slicer is used on every single sheet. Now I want to know, which of the atc7 codes are related via table 2 atc5--> icd-->atc7.

 

Example:

A01AB relates to following icds ( yes I know, there is no relationship in the data model)

 

E11

E13

 

Now I want to find all atc7 codes, related to E11 or E13 (therefor I use table 2 as well)

 

A01AB01

A01AB07

A01CC05

A02DD01

 

How should the result looks like?

 

Well, I just need a straigt table (later a bar chart) with columns (atc7, sum(amount), sum(value)), but thats the problem. By selecting A01AB in the slicer, I will just get A01AB01 and A01AB07. How can I solve this?

 

Here is my current formula:

 

 

 

sum of amount related atc7 =
var vatc5 = VALUES(table1[atc5])
var vtable_icd = CALCULATETABLE(VALUES(table2[icd]);table2[atc5]=vatc5)
var vtable_atc7 = CALCULATETABLE(VALUES(table2[atc7]);table2[icd] IN vtable_icd)

 

return

CALCULATE(sum(table1[amount]];ALL(table1[atc5]);table1[atc7] IN vtable_atc7)

 

 

I get the sum of amount of the A01AB01 and A01AB07, but unfortunalety not for the other two ones.

 

Hope you got it and find a way to help me.

 

THANKS! 😉

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@addicted87 , not very clear. You need to try something like this

Measure =
var _act7 = summarize(allselected(Table1, Table1[Act7)) //because of AC5 and allselected should get ACT7
return
calculate(count(Table2[act]), filter(Table2, Table2[act7] in _act7))

 

or refer treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@addicted87 , not very clear. You need to try something like this

Measure =
var _act7 = summarize(allselected(Table1, Table1[Act7)) //because of AC5 and allselected should get ACT7
return
calculate(count(Table2[act]), filter(Table2, Table2[act7] in _act7))

 

or refer treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

I know that this one is really strange. I try to explain it more detailed.

 

ATC5 is a code for a pharmaceutical ingredient and it stands for a group of more detailed subgroup of igredients. So one ATC5 has 1...n ATC7 Codes and is always of length = 5.

 

ATC 7 is a subgroup of ATC5, so it is more detailed. Its of lenght 7. Lets imagine, your head is aching. Your doctor prescibes you a drug thats makes you feel better. There is of course not just one drug against  headache. Every of this drugs have the same group of ingredients, but while product A got A01AB03, another one has A01AB05. There are several diseases, which are cured with this ingredients and only this one. So you could say, if a patient got a drug with A01AB he has got headache.

 

Now, there are several forms of headache. Each form has an icd. like E11 or E13. So I'm interessted in all forms of headaches and not the one, related to only A01AB.

 

Thats why I select a ATC5, which means, I select one particual form of headache. But I need now all the other forms of headches as well, thats why I'm looking on the icds and furthermore to all ATC7 (and of course ATC5 as well) which are related to headaches. Because there could (and there are) other pharmaceutical ingredients not equal A01AB, doctors prescribe when patients got headaches. More clearly now?

 

Of course I could do a  table1 left join table2 on atc5, but this way my table 1 would blow up so hard, because if there is one ATC5 in table1 and 3 ATC5, related via icd to the ATC5 of table1, i would get 3 instead of 1 rows in table 1.

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.