My table has the first 4 columns and I want to either create a calculated column/use some table functions to find the minimum paid amount per invoice virtually, then sum them up by vendor.
I tried SUMX(VALUES(‘Table’[Invoice ID]),MIN(‘Table’[Amount])), but it’s not correct results.
I watched the SUMX/MINX videos but I guess I’m still not getting it.
Update:
I tried this:
Total Min Amount = SUMX(SUMMARIZE(‘Table’,‘Table’[Vendor],‘Table’[Invoice ID],“Min”,MIN(‘Table’[Amount])),[Min])
It worked. but is this the right/most efficient way to do it? How would you guys do it?
Your approach produces the right result via nice, compact code - well done!
When I tackle these sorts of problems, I usually use a slightly different approach via variables containing virtual tables. It produces a longer measure than yours, but for me at least it’s a little easier to envision what’s going on.
Total Min Amounts by Vendor =
VAR vTable =
ADDCOLUMNS(
'Table',
"MinAmtByInvoice", [Min Amount by Invoice]
)
VAR vTable2 =
SUMMARIZE(
vTable,
'Table'[Vendor],
'Table'[Invoice ID],
[MinAmtByInvoice]
)
RETURN
CALCULATE(
SUMX(
vTable2,
[MinAmtByInvoice]
),
ALLEXCEPT(
'Table',
'Table'[Vendor]
)
)
In the graphic below, 1 corresponds to the first variable in my measure, 2 to the second variable, and 3 to the RETURN statement:
Not better or worse than your approach, just different (though there may be performance differences, if that’s relevant for you). That’s one of the fascinating things about Power BI - 6 different people would probably give you 6 somewhat different, yet correct solutions to this problem
Thank you for such a detailed response and please see attached file.
When we use variables in a measure, what context they are evaluated under? do filters, for example date slicer, affect them?
I modified your file a little bit by adding a date column, then created a quick date table and vendor table using distinct(). Now I use date as a filter and have it on exactly 3/25/2018, the 2 methods are giving different results. In this case, in your method:
1.Dose vtable get filtered by date/any other filters first, then add a new column or the filters do not affect it(meaning, it just adds a column on the entire base table)?
2. How do I modify it so that it takes into consideration of date or any other dimension filters that I may have on the pane?
The reason for the discrepancy between the two approaches after you made the modifications was not due to the use of variables. The culprit was the measure that calculated minimum amounts by invoice. When you introduced the date slicer, the highlighted line need to be added to the ALLEXCEPT() function to keep it from overriding the filter from the slicer:
With that change, both methods now produce identical results:
