Calculate Sum over two columns

[EnrikeMles]

Hi

I'm relatively new, I'm working over a dashboard, and have 2 tables, and the relationship of the tables is Many to Many. 

I need to calculate the percentages of the rows over the columns ( H/T ). I already tried to create a new table with SUMMARIZE, grouping by T column, but I tried to aggregate the H row to the first table using RELATED, so I could have another measure of the total and do the division, but Power BI throws me an error that "the column doesn't have a relationship to any table available in the current context"

So, how I can have the total per row per column? So, I'm looking for the value in the different intersections H1-T1, H2-T1, so I can calculate the percentanges and then work in a graph.

Thanks in advance. Regards.

[Icey · Accepted solution]

Hi @EnrikeMles ,

1. RELATED function can't work with Many-to-Many relationship.

2. In your .pbix file, you can create column "Drain" like below, and "IF..." is not needed.

Drain = RELATED(DrainsClasification[Clasification])

In addition, you can just use the "Clasification" column in "DrainsClasification" table.

3. Is this what you want?

Measure = [TiempoHanmiHA] / SUMX ( ALLSELECTED ( TMS_Eventos[Drain] ), [TiempoHanmiHA] )  

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[EnrikeMles]

@Icey 

Yes, that's what I was looking for, I though I needed to get a measure grouped by Drain to get that percentage.

Thank you so much.

[Fowmy]

@EnrikeMles 

Did not quite understand the expected results?

However, it is better to avoid Many-To-Many by reconfiguring a bridge table please refer to:

https://www.seerinteractive.com/blog/join-many-many-power-bi/ 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[EnrikeMles]

@Fowmy 

Hi, I tried to replicate the bridge table, and I'm pretty sure that I follow all the steps correctly, but the relationships between the new Brigde table and the other 2 tables are still Many to Many.

Any hint of what could be wrong.

[Fowmy]

@EnrikeMles 

Your bridge table is supposed to have uniques values, arrange it in such a way and link with the other table, you may share a sample pbix file to check and resolve.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[EnrikeMles]

@Fowmy 

Hi, I encountered the error, in the Bridge table there is some values duplicated, ones in lowercase and ones in uppercase, so I removed manually, but I don't quite seem to find the values the I need.

I tried to remove registers from the pbix to get a sample data, but the size is still 150Mb, is there another way to share it?

So, for the record, the value for T1, T2 and T3 is a measure, and the formula is:

T1 = CALCULATE(SUM(Table1[Time]), 'Table2'[Technology]="T1")

And then, what I'm tried to figure out, is how to get the SUM of "Time", but grouped by "Technology" and "Drain"; the Drain is in Table 1 like the Time, but when I tried to use a formula like the one above, I got the "Drain Time" per all technologies instead of the the "Drain Time" per "Drain" per "Technology".

Sorry for the lot of questions.

[Icey]

Hi @EnrikeMles ,

It is suggested to create a dummy sample .pbix file with the same data structure and model. And then upload it to OneDrive for Business and paste the link here.

Best Regards,

Icey

[EnrikeMles]

@Icey 

Ok, I uploaded to my personal OneDrive because of the sharing permissions from the Business. So, here is the link of the pbix with the loaded data. 

The table that I'm working is on Page 1.

https://1drv.ms/u/s!AraR3pepXi7hgb8QbBWTveFbts7P9A?e=lJgymV

Thanks in advance.