calculation with different groups

[nicolasawa]

hello,

Théorically speacking,

for each rows, I have a number ("a") that corresponds to the row and an other ("b") that corresponds to the group of rows to which the row belong.

The problem is that when I do mesures on this two numbers (sum(a)/sum(b)) and that I filter datas in my visuals, powerBI sums automatically all the "a" of the current filter (no problem with that) and all the "b" for each "a". I only want to sum one b per "a".

Practically speacking,

previously to this, I had x materials for which I knew the type and the life time. I grouped them by type and life time and counted them by does groups (this is "a"). then I grouped them only by type and counted them (this is "b"). And I joined the 2 groupements to have for each row, "a" and "b". So I have multiple "b" that are the same for different "a" and when I sum, I want to count "b" only one time.

Note that some "b" from different groups might be the same value but from different groups so in this case, I want to sum one "b" per group.

The goal is that the user select some types of materials in the filters, and the the tool automatically calculates number of the materials from this types and this life time divided by the number of the materials of this types.

Let me know if I'm not clear about one point.

Thank you,

Nicolas

[v-jialluo-msft]

Hi  @nicolasawa ,

According to your description, you need to sum up the total number of material types grouped in different ways, and then divide it.
But according to your method, it seems that the final calculation result is 1.
If my understanding is wrong, please point it out.

Best Regards,
Gallen Luo

[Mahesh0016]

@nicolasawa Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AlB]

Hi @nicolasawa 

Can you show an example based on actual data of what you are trying to accomplish? And ideally share the pbix

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[nicolasawa]

hello, for exemple,

I have 4 materials of the red type. One have a lifetime of 2 years and the 3 others have the life time of 3 :

and 3 materials of the blue type. all three have a lifetime of 2 years

type	lifetime (year)	number of material by type and lifetime	total number of material by type
red	2	1	4
red	3	3	4
blue	2	3	3

when the user filter by type red and blue, the mesure I want is the sum of all the "number of material by type and lifetime" divided by the "total number of material by type". But the first two  rows of the last column are going to be counted 2 times instead of one. I want it to be counted only one time. The result of the mesure I want is : (1+3+3)/(4+3). Not (1+3+3)/(4+4+3)

[AlB]

@nicolasawa 

This does not make a lot of sense. The information in your rightmost column is redundant, you can extract it from the number of material by type and lifetime column.  The calculation you propose will always yield 1, so I am guessing you want something else

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.