TOTAL for grouping data by using different filter [Solved]

[tomlei]

I will try to explain the scope:

Within BI I want to calculate the TOTAL loss in kg for different types of defects over a certain time range by grouping the data for the same scrap plate and selecting the highest defect class.

In detail:
- I have a list of scraped plates with the related defects (classes and types)
- it could be that one scrap plate with the plate ID is more than one times in the list, because there are different defect types and/or classes on it
- I need to select the highest defect class on each plate
- I need the TOTAL loss in kg for each defect type over the time period
- I want to visualize these TOTAL numbers in a pareto chart

I calculate a sample data by using excel - please see the attachment.

If someone able to help me with that?

[tomlei]

Hello All
let me rephrase it in the 2nd version - see attachment

Regards
Tom

[rwfigtree]

m:=var x = SUMMARIZE(Table2,Table2[Tab1_ID],"avg",AVERAGE(Table1[f2]))
return SUMx(x,[avg])

[tomlei]

m:=var x = SUMMARIZE(Table2,Table2[Tab1_ID],"avg",AVERAGE(Table1[f2]))
return SUMx(x,[avg])

Today, I was able to check the forum and I have used your measure - it works !! Many thanks. But, I am still struggling to understand what happened there...

Here the measure and attached the chart result.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

xm_GBDefGrp_2 =
VAR _x = SUMMARIZE(Tab_GB_GTCDefects,Tab_GB_GTCDefects[GTCDefect_GTCRowID],"avg",AVERAGE(Tab_GB_GTCDist[x_WeightSqmTotal]))
return
SUMX(_x,[avg])


chart_DefectLosses.JPG (14.82 KiB) Viewed 493 times

table_DefectLosses.JPG (16.03 KiB) Viewed 488 times

Do you have an idea how I can create a Pareto Chart from the data? Maybe you can help me with that too. I did it with Rank in another case, but I dont knwo how can I get the measure result in a table to add the Rank column for the pareto function/chart.

thanks and regards
Tom

[rwfigtree]

Hi just been mucking around with some ideas not fully there but you might be able to take it from here or someone else can put in 2 cents.

Changed original formula to something a little simpler.

mResult:=var x = VALUES(Table2[Tab1_ID])
return CALCULATE(SUM(Table1[f2]),FILTER(Table1,CONTAINSROW(x,Table1[IDTab1])))

Also might need to use calculated columns to do the chart, but was getting stuck with circular references when doing the ranking. So fun times for you.

Be interested if you come up with solution please post.

See attached.

[rwfigtree]

Always been a bit interested in material sciences, did civil engineering at uni a zillion years ago.
Who do you work for if you don't mind me asking.

[tomlei]

Always been a bit interested in material sciences, did civil engineering at uni a zillion years ago.
Who do you work for if you don't mind me asking.

Hello and sorry for my late response, in the meantime I was trying to find a solution for the Pareto topic - without success so far.
Regarding your question about my work, I am working for a glass maker. We are producing float glass in Europe. The defects I am analyzing are standard melting defects in the melting process.

regards
Tom