Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I have a table in Power BI. This table contains project numbers, clerks, invoice numbers, amount and a date.
There can be several invoice numbers with different date for a project.
Now I would like to create a report that contains 2 data slicers. Once for the date (from-to) and once for the clerk.
In addition, the amount should be totaled out.
So far no problem. However, I would like only the highest invoice number per project to be included in the total.
Here's an example:
When filtering by date January 01, 2021 and clerk A
Result: 80 (invoice number 1 and 8 )
When filtering by date January 01, 2021 and clerk B
Result: 15 (invoice number 4)
When filtering by date January 02, 2021 and clerk A
Result: 25 (ONLY invoice number 3 and 7, NOT also number 2)
When filtering by date January 02, 2021 and clerk B
Result: 10 (ONLY invoice number 6, NOT also number 5)
Hope it's understandable 🙂
How exactly can I ensure that only the highest invoice number per project is taken into account in the amount, in addition to the filtered date and person responsible?
thank you for your help 🙂
LG
Solved! Go to Solution.
Hi, @Meeresblume
Try formula as below, it may be a bit clumsy:
amount of max invoice number =
VAR _max_invoice_number =
CALCULATE (
MAX ( 'Table'[invoice number] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project number] = MIN ( 'Table'[Project number] )
)
)
RETURN
CALCULATE (
MAX ( 'Table'[amount] ),
FILTER ( 'Table', 'Table'[invoice number] = _max_invoice_number )
)
Result =
VAR tab =
SUMMARIZE (
'Table',
'Table'[Project number],
"s_amount", [amount of max invoice number]
)
RETURN
SUMX ( tab, [s_amount] )
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
@Meeresblume , Try a measure like this with project number and date in visual
Measure =
VAR __id = MAX ('Table'[Project Number] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Project Number] = __id )
CALCULATE ( Sum ('Table'[Amount] ), VALUES ('Table'[Project Number] ),'Table'[Project Number] = __id,'Table'[Date] = __date )
Thanks for your answer.
However, the measure would not work.
I need the highest offer number per project, but depending on my filter with the date and the person responsible, the amount that is totaled.
The way I interpret the measure from you, I would only make this out by attaching the project number and the max date. But not from my filtered date + clerk.
Hi, @Meeresblume
Try formula as below, it may be a bit clumsy:
amount of max invoice number =
VAR _max_invoice_number =
CALCULATE (
MAX ( 'Table'[invoice number] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Project number] = MIN ( 'Table'[Project number] )
)
)
RETURN
CALCULATE (
MAX ( 'Table'[amount] ),
FILTER ( 'Table', 'Table'[invoice number] = _max_invoice_number )
)
Result =
VAR tab =
SUMMARIZE (
'Table',
'Table'[Project number],
"s_amount", [amount of max invoice number]
)
RETURN
SUMX ( tab, [s_amount] )
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |