Forum Discussion
Helper IIAggregation Problem with Power BI
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?
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
7 Replies
- amitchandak
Super User
thebigwhite , Create a new column in Table1
Maxx(filter(Table2, Table2[item] = table1[item]), Table2[Value]) *Table[Qty]
- mangaus1111
Solution Sage
- 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!!
thebigwhiteI 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
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
