Forum Discussion

dpc_development's avatar
dpc_development
Icon for Helper III rankHelper III
4 years ago

Distinct SUM from dimension table column

I have a dimension table with a list of events and each event has a Spend amount.

 

I am creating a summary table and would like to show the total spend within the current context.

 

Currently, I am calculating it as follows, by getting a list of distinct Event Ids in the current fact table context, filtering the Events table with those ids, and then summing up the Spend column

 

Spend =
CALCULATE(
ROUND(SUM(Events[Spend]), 2),
Events[Event Id] IN DISTINCT('Event Performance'[Event Id])
)

 

This works, but was wondering if there is a better way to do this, perhaps by using the RELATED or RELATEDTABLE DAX formula, or some other better method.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    dpc_development , Try like

     

    Spend =
    CALCULATE(
    ROUND(SUM(Events[Spend]), 2),
    not(isblank('Event Performance'[Event Id]))
    )

     

    if they are joined no need of related

    • dpc_development's avatar
      dpc_development
      Icon for Helper III rankHelper III
      4 years ago

      I am afraid that doesn't work. It just shows the total of all the event spends, in all the rows. I guess it's because there is no relation between the Events (dimension) table and Calendar (dimension) table, and Event Performance is my fact.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi dpc_development ,

     

    The ID column establishes a one-to-many relationship between two tables, and directly finds the sum value of the corresponding ID field.

     


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • dpc_development's avatar
      dpc_development
      Icon for Helper III rankHelper III
      4 years ago

      The problem is with the column to be summed being in the dimension table, not fact table.

      As can be seen in the above relationship diagram, an event can take place over multiple days, and on each day, the fact table (Event Performance) has Revenue and Gross profit. The Spend in the Event table is how much was spent on that Event.

       

      So eventually I have to total up all the Revenue and Gross Profit from the fact table and subtract Spend from the Event dimension table.

       

      This is why I had a complex formula for Spend, but was wondering whether it could be simplified.