Forum Discussion
Aggregation 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
thebigwhite , Create a new column in Table1
Maxx(filter(Table2, Table2[item] = table1[item]), Table2[Value]) *Table[Qty]
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!!
I am sorry I haven't mentioned that the two tables are connected Item<->Value
is the relationship 1 to many?
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
