Highest Sales Month-wise of any Year

Anything related to PowerPivot and DAX Formuale
amisandip
Posts: 1
Joined: Sat Apr 29, 2017 8:47 am

Highest Sales Month-wise of any Year

Postby amisandip » Sat Apr 29, 2017 8:52 am

I have a fact table with following columns - ClusterManager, BranchManager, RelationshipManager, Product, Date, SalesAmount.

And I have a Calendar table with following columns - Date, MonthNumber, MonthName, Year.

Now based on selection of Year, Product and ClusterManager I want to show selected ClusterManager's maximum sales ever and also in which month and year he achieved that feat.

How to do this using DAX?

Oxenskiold
Posts: 30
Joined: Tue Jan 05, 2016 10:38 pm

Re: Highest Sales Month-wise of any Year

Postby Oxenskiold » Tue May 02, 2017 7:57 am

Hi Amisandip,

I'm not sure exactly what you mean. To me it doesn't make sense to say:

"Now based on selection of Year, Product and ClusterManager I want to show selected ClusterManager's maximum sales ever"

when you at the same time end the sentence with

"and also in which month and year he achieved that feat"

In the last part you introduce a new attribute 'month'. To have it make sense you'll have to include all attributes in both measures otherwise what you find in the first may not be reflected correctly in the second.

If that is what you want you can try these measures:

Code: Select all

Maximum sales :=
IF (
    ISFILTERED ( facttable[Clustermanager] ),
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE (
                facttable,
                facttable[Clustermanager],
                facttable[product],
                calendar[year],
                calendar[month]
            ),
            "Amount", CALCULATE ( SUM ( facttable[salesamount] ) )
        ),
        [Amount]
    )
)



Code: Select all

Maximum sales year month :=
IF (
    ISFILTERED ( facttable[Clustermanager] ),
    CALCULATE (
        FORMAT ( VALUES ( calendar[year] ), "0000" ),
        TOPN (
            1,
            SUMMARIZE (
                facttable,
                facttable[Clustermanager],
                facttable[product],
                calendar[year],
                calendar[month]
            ),
            CALCULATE ( SUM ( facttable[salesamount] ) )
        )
    )
        & " - "
        & CALCULATE (
            VALUES ( calendar[MonthName] ),
            TOPN (
                1,
                SUMMARIZE (
                    facttable,
                    facttable[Clustermanager],
                    facttable[product],
                    calendar[year],
                    calendar[month]
                ),
                CALCULATE ( SUM ( facttable[salesamount] ) )
            )
        )
)


it's not possible for me to test the measures so give them a run and let us know if this was what you intended

best regards
Jes.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest