Forum Discussion

jimrosser's avatar
jimrosser
Helper III
1 year ago
Solved

Table Sum Sales by State

Hi I have been trying to get this measure to work in a PBI report.   But it's still not working My report is direct query so I cannot post, but I have two tables store_sales and store_dim I want to sum the sales amount for each state and item if possible the item no is in the sales table.  Thanks in advance. 

 

StateSales =

VAR vTable =
    SUMMARIZE(
        store_sales,
        store_sales[ty_sales_amt],
        store_dim[state_prov_cd]
    )
   
VAR Result =
IF(
    HASONEVALUE( store_dim[state_prov_cd] ),
    MAXX(
        vTable,
        store_sales[ty_sales_amt]
    ),
    SUMX(
        vTable,
        store_sales[ty_sales_amt]
    )
)

RETURN
Result
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    jimrosser , Try using

     

    DAX
    StateSales =
    CALCULATE(
    SUM(store_sales[ty_sales_amt]),
    ALLEXCEPT(store_dim, store_dim[state_prov_cd])
    )

     

    And

    DAX
    StateItemSales =
    CALCULATE(
    SUM(store_sales[ty_sales_amt]),
    ALLEXCEPT(store_dim, store_dim[state_prov_cd], store_sales[item_no])
    )

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jimrosser ,

     

    SUMMARIZE(store_sales, store_sales[ty_sales_amt], store_dim[state_prov_cd]) will group by sales amount and state instead of aggregating by state and item.

    Please try like:

    StateSales = 
SUMX (
    SUMMARIZE (
        store_sales,
        store_dim[state_prov_cd],
        store_sales[item_no]
    ),
    CALCULATE ( SUM ( store_sales[ty_sales_amt] ) )
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    jimrosser , Try using

     

    DAX
    StateSales =
    CALCULATE(
    SUM(store_sales[ty_sales_amt]),
    ALLEXCEPT(store_dim, store_dim[state_prov_cd])
    )

     

    And

    DAX
    StateItemSales =
    CALCULATE(
    SUM(store_sales[ty_sales_amt]),
    ALLEXCEPT(store_dim, store_dim[state_prov_cd], store_sales[item_no])
    )

  • Thejeswar's avatar
    Thejeswar
    Super User
    1 year ago

    jimrosser ,

    A Simple measure should be enough for this purpose. This measure will be dynamically responding to the columns and slicers applied across this measure

    The Below is the measure you can use. For specific cases, this measure will change.

    Sales = SUM(store_sales[ty_sales_amt])

     

    For your context specific answer, you may have to share some sample dummy data from both those tables and the expected output from that dummy data

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jimrosser ,

     

    SUMMARIZE(store_sales, store_sales[ty_sales_amt], store_dim[state_prov_cd]) will group by sales amount and state instead of aggregating by state and item.

    Please try like:

    StateSales = 
SUMX (
    SUMMARIZE (
        store_sales,
        store_dim[state_prov_cd],
        store_sales[item_no]
    ),
    CALCULATE ( SUM ( store_sales[ty_sales_amt] ) )
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum