Aggregation Problem with Power BI

[thebigwhite]

Hello I have the following issue: 

I have two tables. 

One with Quantity and another with values

Basically the user needs a dynamic result: 

The want to have the minimum quantity for each Item  by Group. 

This must be multiplied by the Value of the relative Table. 

The want to be free to select on which group evaluate the analysis. 

Can you help me? 

[v-jayw-msft · Accepted solution]

Hi @thebigwhite ,

Check the formulas:

itemview = MINX(TableA,TableA[Qty])*CALCULATE(SUM(TableB[amount]),FILTER(TableB,TableB[Value]=SELECTEDVALUE(TableA[Item])))

groupview = CALCULATE(MIN(TableA[Qty]),ALLEXCEPT(TableA,TableA[Item],TableA[Group]))*CALCULATE(SUM(TableB[amount]),FILTER(TableB,TableB[Value]=SELECTEDVALUE(TableA[Item])))

Best Regards,

Jay

[thebigwhite]

I am sorry I haven't mentioned that the two tables are connected Item<->Value

[mangaus1111]

is the relationship 1 to many?

[mangaus1111]

The same formula works for the Group View as well

[mangaus1111]

Hi @thebigwhite ,

MINX(TableA;TableA[Qty]*RELATED(TableB[Value]))  is a measure (not a calculated column). It works only if you create a 1 (Table B) to many (Table A) relationship.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Resolver!!

[mangaus1111]

Hi @thebigwhite ,

for Item View you can use

MINX(TableA;TableA[Qty]*RELATED(TableB[Value]))

[amitchandak]

@thebigwhite , Create a new column in Table1

Maxx(filter(Table2, Table2[item] = table1[item]), Table2[Value]) *Table[Qty]