Forum Discussion

thampton's avatar
thampton
Icon for Helper III rankHelper III
7 years ago

SUMMARIZE DAX help needed

I have a data model in SSAS that i am bringing into Power Bi to create visuals. I have an Sales table with a connected financials table, based on the Sales ID. I also have dates in the sales table. 

 

Currently i have create a summary table using SUMMARIZE. I would like a column to show the TOP Sales Order Month... So if Jane Doe had a customer for 3 months, and they did $100 the first month, $50 the second, and $150 the third, i would like the have a column showing BestMonth as header and display $150. 

 

How would i format that DAX staying consistent with the SUMMARIZE ( Sales Table, SalesID, "BestMonth", DAX HELP)

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi thampton 

    It seems you import data into Power BI.

    If so, assume [sale id] in Sales table and [sale id] in financials table have "many to one" relationship

     

    In  Sales table

    Create calculated columns

    year = YEAR('Sales table'[date])

month = MONTH('Sales table'[date])

    Create measures

    sales_monthly =
CALCULATE (
    SUM ( 'Sales table'[sales] ),
    FILTER (
        ALLSELECTED ( 'Sales table' ),
        'Sales table'[year] = MAX ( 'Sales table'[year] )
            && 'Sales table'[month] = MAX ( 'Sales table'[month] )
            && 'Sales table'[sales id] = MAX ( 'Sales table'[sales id] )
    )
)

Max sales =
MAXX (
    FILTER (
        ALLSELECTED ( 'Sales table' ),
        'Sales table'[sales id] = MAX ( 'Sales table'[sales id] )
            && 'Sales table'[year] = MAX ( 'Sales table'[year] )
    ),
    [sales_monthly]
)

Best month =
CALCULATE (
    MAX ( 'Sales table'[month] ),
    FILTER (
        ALLSELECTED ( 'Sales table' ),
        'Sales table'[sales id] = MAX ( 'Sales table'[sales id] )
            && 'Sales table'[sales_monthly] = [Max sales]
    )
)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • thampton's avatar
      thampton
      Icon for Helper III rankHelper III
      7 years ago

      v-juanli-msft 

       

      Thank you for the reply. If i want to write this dax as a calculated column (so i dont have to do the measures in PBI desktop). How would i configure that? 

       

      Currently i have a summary table like below and would just like to add it here...

       

      Sales ID           Total Financials          (NEW COLUMNS)

      1                      100                             Top Month DAX

      2                      200                             Top Month DAX

       

       

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    You should be able to do this pretty easily.  Once you have the tables related by salesID, create the new column in your summary table and set it up like this:

    BestMonth = MAX(RELATED('SalesTable'[sales]))

    Since the table SummaryTable is related to the FinancialsTable by a many:1 relationship on Sales ID, and the FinancialTable is related to the SalesTable by a 1:many relationship on Sales ID, PowerBI is able to make that connection straight to the SalesTable.  As long as the Sales ID is populated, when you use the RELATED function it will return a list of filtered values that are related to the current row. Add in the MAX function to find the top value, and you're there!