Help needed with DAX formula in Desktop pls

[pjandliz]

I am trying to get a DAX formula to work but am stuck so I'm hoping someone can help pls.

I have 3 related tables; a StaffTable (with StaffCode), a SalesBookings table (with SaleId, SaleStaffCode, TeamStaffCode & TeamStaffSaleAmt fields) and a SalesTable (with SaleId and SaleTotal value).

They are related as:

StaffTable <=> SalesBookings by StaffCode = SaleStaffCode

SalesBookings <=> SalesTable by SaleId = SaleId

(I also have a inactive relation for StaffTable <=> SalesBookings by StaffCode = TeamStaffCode)

The SalesBookings table may have multiple rows per SaleId & SaleStaffCode as the SaleTotal is broken down by TeamStaffCode.

My data would look like this:

SalesBookings

SaleId  SaleStaffCode   TeamStaffCode   TeamSaleAmt

1          A100                 A100                   90$

1          A100                 A200                   20$

2          A100                 A100                   90$

2          A100                 A200                   20$       

2          A100                 A300                   20$ 

3          A900                 A900                   90$

3          A900                 A100                   20$

SalesTable

SaleId  SaleTotal

1          110$

2          130$

3          110$

Results required:

SaleStaffCode   SaleTotal

A100                 240$ (ie sum of SaleId's 1 & 2)

A900                 110$

Presently, for both SaleStaffCode I am getting 350$

I'm trying to calculate the total of SalesTable[SaleTotal] by SalesBooking[SaleStaffCode] but not having any luck. I keep getting the total of all SaleTotal for each SaleStaffCode.

Essentially I need to sum the SalesTable[SaleTotal] field (by SalesBooking[SaleStaffCode]) for each unique SaleId row for the SaleStaffCode in the SalesBookings table.

I've tried using TREATAS with DISTINCT to get a distinct list of the SaleId's and use this as a filter on the SalesTable but no luck.

This is essentially what I've last tried as a reference. 

I'm sure its something simple I'm missing as my DAX is rusty.

SalesBySalesperson:=
CALCULATE(
  SUM(SalesTable[SaleTotal]),
  TREATAS(
    DISTINCT(SalesBookings[SaleId]),
    SalesTable[SaleId]

  )
) 

My visual has the SaleStaffCode field in it so I'm expecting this to be filtering the SalesBookings data too.

In principle, I should be able to sum the TeamSaleAmt (as this should tie to the SalesTotal), however I have instances where part of the SalesTotal has not been allocated (so the sum of TeamSalesAmt <> SalesTotal) which is why I'm trying to sum the SalesTotal (as it's the true total).

Thanks in advance for your help, PJ.

[v-alq-msft]

Hi, @pjandliz 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

SalesBookings:

SalesTable:

There is a relationship betweent two tables.

You may create a measure as below.

Result = 
SUMX(
    SUMMARIZE(
        DISTINCT(SalesTable[SalesId]),
        SalesTable[SalesId],
        "Result",
        SUM(SalesTable[SaleTotal])
    ),
    [Result]
)

Result:

Best Regards

Allan

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

[pjandliz · Accepted solution]

Thanks for your help Allan.

Your diagram misses the StaffTable relationship. This has a 1:Many from StaffTable to SalesBookings, and SalesBookings has a M:1 to SalesTable.

I have finally resolved this by changing the SalesBookings to SalesTable relationsip to a two way relationship. I think the 2 relationships out of SalesBookings - both being the Many side - was causing the filtering not to work as expected.

[amitchandak]

@pjandliz , that is also the sum of TeamSaleAmt in the bookings table. Need to go to SalesTable ?

Try a measure like

SalesBySalesperson:=
var _tab = summarize(allselected(SalesBookings),SalesBookings[SaleId])
return
CALCULATE(
SUM(SalesTable[SaleTotal]),
filter(SalesTable, SalesTable[SaleId] in _tab) )

[pjandliz]

Thanks for your reply amitchandak.

I tried your formula and am getting the same result as my formula.

The problem appears to be the SaleStaffCode isn't acting as a filter on the formula when it appears in a visual.

If I add a table visual and add the SaleStaffCode in one column and the formula in another, I'm still getting the total for all SaleStaffCode's as the result for each code.

This set of tables are a subset of tables within a bigger data model - I'm trying to incorporate these tables and visuals into an existing dashboard - so not sure if something outside of the visual/DAX is causing this.

On the face of it, this should work which is why it's been driving me crazy.

I''m not sure if this helps explain the situation better.