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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nicolasawa
New Member

calculation with different groups

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

5 REPLIES 5
v-jialluo-msft
Community Support
Community Support

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

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

AlB
Super User
Super User

Hi @nicolasawa 

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

 

SU18_powerbi_badge

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.

 

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

typelifetime (year)number of material by type and lifetimetotal number of material by type
red214
red334
blue233

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)

@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

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors