Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! I'm struggling trying to present a "top month" view for production for all time. i.e. one producer's best month may have been April 2018 while another's best production month may have been September 2020. I'm using
Solved! Go to Solution.
Please try this measure expression. It returns both the max amount and the date it occurred on. If there is a tie, it will concatenate them together.
Best Month =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
'Table',
'Table'[CloseDate],
'Table'[LoanOfficerName]
),
"cProduction",
CALCULATE (
SUM ( 'Table'[Production] )
)
)
VAR vMaxProd =
CONCATENATEX (
TOPN (
1,
vSummary,
[cProduction], DESC
),
'Table'[CloseDate] & " - "
& FORMAT (
[cProduction],
"Currency"
),
"; "
)
RETURN
vMaxProd
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this measure expression. It returns both the max amount and the date it occurred on. If there is a tie, it will concatenate them together.
Best Month =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
'Table',
'Table'[CloseDate],
'Table'[LoanOfficerName]
),
"cProduction",
CALCULATE (
SUM ( 'Table'[Production] )
)
)
VAR vMaxProd =
CONCATENATEX (
TOPN (
1,
vSummary,
[cProduction], DESC
),
'Table'[CloseDate] & " - "
& FORMAT (
[cProduction],
"Currency"
),
"; "
)
RETURN
vMaxProd
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you! This solution worked best for my purposes.
Can you provide some sample data to work with?
Here's an example. CloseDate is joined on a date table.
In this last view I'd like "MonthName" to display "2019 - Dec" for Lender 1, "2019 - Dec" for Lender 2, and "2020 - Jan" for Lender 3.
Please let me know if that doesn't make sense, thanks!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi! I came up with this...
Best Production Month:=VAR MonthlySummary =
GROUPBY(
Production,
Production[LoanOfficer],
Production[CloseDate],
"MonthlyProduction",
SUMX(
CURRENTGROUP(),
Production[Production]
)
)
VAR TopPerformingMonths =
TOPN(
1,
MonthlySummary,
[MonthlyProduction],
DESC
)
RETURN
CALCULATE(
FIRSTNONBLANK(
Production[CloseDate],
TRUE()
),
TopPerformingMonths
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |