Forum Discussion

jmazzola31's avatar
jmazzola31
Frequent Visitor
2 years ago

Accounts Sold Measure Performance

Hello All,

 

I am trying to calculate a measure from the main Sales Table in my report. The Sales Table is the fact table and the model follows a star schema. The Sales Table contains 4.7 million rows.

 

The measure I am trying to calculate is an  "Account Sold Measure". The purpose of this measure is to see the numbers of customers that purchased anything > 0 in the current filter context. For example, if brand A is displayed in a Table Visual and the filtered time period is YTD I want the metric to show the number of accounts that had > 0 purchases year to date for brand A. Listed below is the measure I am currently using to find this number.

 

Accounts Sold Measure =
VAR Account_Sold_Table =
ADDCOLUMNS (
SUMMARIZE ( 'Sales Table', 'Sales Table'[Account #] ),
"Accounts Sold Summary", IF ( ROUND ( CALCULATE ( SUM ( 'Sales Table'[Cases Column] ) ), 2 ) > 0, 1, 0 )
)

RETURN
SUMX ( Account_Sold_Table, [Accounts Sold Summary] )

 

This measure works but it is slow. I am wondering if there is anything I can do to clean up the dax and make it perform faster.

 

Any help is appreciated. Let me know if more information is needed.

Need Help

5 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi jmazzola31 possible solution to count number of customer(if account in Sales Table is customer id) is measure as following

    Count Account: DISTINCTCOUNT('Sales Table'[Account #])

    Did I answer your question? Kudos appreciated / accept solution!

  • jmazzola31's avatar
    jmazzola31
    Frequent Visitor
    2 years ago

    how would I account for the fact I need a table with accounts that have > 0 cases sold?

     

    • some_bih's avatar
      some_bih
      Community Champion
      2 years ago

      Hi jmazzola31 assumption for my proposed solution is fact that in your fact table there are already ONLY sold items (customers/accounts key column) sold AND amount of sales is always >0.

      I hope this is in your case?

      • jmazzola31's avatar
        jmazzola31
        Frequent Visitor
        2 years ago

        the fact table holds orders of all types. The orders can be 0, negative or positive. 

         

        That is why I think I need to create a summarize variable to act is a sub query on the account number and then sum all cases on the account and then check to see if those net cases are greater than 0 or not.