How to Display MOM Sales Increase Based on Users' Choice of Month?

[wang51437]

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!

[v-kkf-msft · Accepted 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.

[wang51437]

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?

[v-kkf-msft · Accepted solution]

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

[wang51437]

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?

[v-kkf-msft]

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.

[wang51437]

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. 

[v-kkf-msft · Accepted 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.