Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
I have got a table that contains the monthly sales of a few companies as follows:
Company | January | February | March | April | May | June |
A | $ 517.00 | $ 537.00 | $ 572.00 | $ 812.00 | $ 614.00 | $ 626.00 |
B | $ 413.00 | $ 266.00 | $ 733.00 | $ 924.00 | $ 418.00 | $ 378.00 |
C | $ 535.00 | $ 202.00 | $ 631.00 | $ 723.00 | $ 341.00 | $ 582.00 |
D | $ 520.00 | $ 437.00 | $ 326.00 | $ 521.00 | $ 831.00 | $ 723.00 |
I've calculated the MOM change by company using this formula
SUM([SALES]) - CALCULATE(SUM([SALES]),DATEADD([DATE],-1,MONTH))
Then I added a Month slicer to my Power BI dashboard which would allow the users to choose their month of interest.
Now I'd like to also insert a card to the dashboard to show the name of the company that had the largest MOM sales change for the month of the users' choice. For instance, if the user chose April in the dropdown, the company to display on the card should be A as it had the highest change in sales from March to April. If the user chose June, C should display on the card.
I can't figure out how to make the company name display by the users' choice of month. Could someone please help? Thanks!
Solved! Go to Solution.
Hi @wang51437 ,
Please try these measures.
largestMOM = MAXX ( ALLSELECTED ( 'Table'[Company] ), [MOM] )
Company_largest MOM =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER (
VALUES ( 'Table'[Company] ),
[MOM] = [largestMOM]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @wang51437 ,
Please try these measures.
largestMOM =
VAR MaxDate =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
RETURN
CALCULATE (
MAXX ( ALLSELECTED ( 'Table'[Company] ), [MOM] ),
FILTER ( Dates, FORMAT ( 'Dates'[Date], "yymm" ) = FORMAT ( MaxDate, "yymm" ) )
)
Company_largest MOM =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER (
SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Company], Dates[Date].[Month] ),
[MOM] = [largestMOM]
)
)
Best Regards,
Winniz
Any chance to default the display to the company with the highest MOM change from the most recent month if the user doesn't choose a month? For instance, now is June. If the user of the dashboard doesn't choose a month in the dropdown, the card will display the company that has the largest increase in sale from May to June and the corresponding amount of the increase?
Hi @wang51437 ,
Please try these measures.
largestMOM =
VAR MaxDate =
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] )
RETURN
CALCULATE (
MAXX ( ALLSELECTED ( 'Table'[Company] ), [MOM] ),
FILTER ( Dates, FORMAT ( 'Dates'[Date], "yymm" ) = FORMAT ( MaxDate, "yymm" ) )
)
Company_largest MOM =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER (
SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Company], Dates[Date].[Month] ),
[MOM] = [largestMOM]
)
)
Best Regards,
Winniz
This is awesome! Thanks!
Is it possible to default the card to display the largest MOM increase company name & amount from the last month if no month is selected from the dropdown?
Hi @wang51437 ,
Please try the measure.
largest MOM =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER (
VALUES ( 'Table'[Company] ),
[MOM] = MAXX ( VALUES ( 'Table'[Company] ), [MOM] )
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That worked! Thank you!
Is there a way to make the card a multi-row card where the first row displays the name of the company with the highest MOM change by users' choice of month, and the secord row displays the corresponding value (increase in MOM sales for the month of choice)? I got stuck at creating a measure that shows the MOM sales increase by users' choice of month.
Hi @wang51437 ,
Please try these measures.
largestMOM = MAXX ( ALLSELECTED ( 'Table'[Company] ), [MOM] )
Company_largest MOM =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER (
VALUES ( 'Table'[Company] ),
[MOM] = [largestMOM]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
93 | |
60 | |
44 | |
35 | |
34 |