Total showing correctly, but individual measure blank

[Mishutka]

After a lot of trouble to try to get a measure to sum on the value in another column, I now have this working except that the measure value isnt showing up where there is no primary amount on a given date. To show what I mean: I have a data table, and I have 3 measures:

1. Sales=[Sum of Sales value]+0 ,

2. AssisterSum = var Assister = values(Table1[Salesperson])
return
IF(HASONEFILTER(Table1[Salesperson]),CALCULATE(SUM(Table1[Sales value]),all(Table1[Salesperson]),Table1[Sales Assist]=Assister)+0,SUM(Table1[Sales value])), and

3. Total Attibutable Sales =[Sales]+[AssisterSum]

I added in the plus zeros to try to force the numbers to display. Unfortunately, although the pivot table correctly shows the Grand Total, the row totals are wrong for AssisterSum and hence Total Attributable Sales on the row for John, who does not have any direct Sales on the date of 08/01.

If I add a blank row for John on the date of 08/01, the measures evaluate correctly. However, in the real dataset, I cant be sure that there will always be a data row for each calendar date for each salesperson.

Can anyone help me with how to modify the DAX measure Assister Sum - I do have a date table available, if that can be part of the solution.

[sturlaws · Accepted solution]

Hi @Mishutka 

I would create new table/dimension for the salespersons, and create to relationships between the salesperson table and the sales table:

Then keep your sales measure as it is, but change the assisting sales measure to this:

Assisting sales =
CALCULATE (
    SUM ( sales[Amount] ),
    USERELATIONSHIP ( sales[Sales assist], Salesrepresentatives[Salesrep] )
) + 0

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Mishutka]

@sturlaws- thanks for this. I thought this would work, so I added this new measure as you suggested: 

CALCULATE(sum(Sales[Sales value]),USERELATIONSHIP(Sales[Sales Assist],Salesrepresentative[Salesrep])) + 0 , 

with this data model, and the output equals sales, not sales assist

---

[sturlaws]

I don't understand what you mean, I have attached a sample report I have created, showing how to solve it

[Mishutka]

@sturlaws- Thanks for your trouble on this, I dont really understand how to explain this, but I have what looks like the same data model with the same data and the same measures, but I am getting a different result. I was originally doing this in Excel, but have transferred to Power BI desktop and I get the same problem. If I knew how to upload my pbix file, I would, but I can't see an easy way. However, my assisting sales measure is 

Assisting Sales = var ThisDate = if(HASONEFILTER('Calendar'[Date]),values('Calendar'[Date]),"")
var SalesThisDate = if(CALCULATE(COUNTROWS(Sales),all(Sales),FILTER(all('Calendar'),'Calendar'[Date]=SELECTEDVALUE('Calendar'[Date])))>=1,0,blank())
return
CALCULATE(sum(Sales[Sales value]),USERELATIONSHIP(Sales[Sales Assist],Salesrepresentatives[Salesrep])) + SalesThisDate

and my tables show

[sturlaws]

There should be two relationships between salesrep and sales. The relationship between Sales[Salesperson] and Salesrepresentatives[Salesrep] should be the active one, while the relationship between Sales[Sales Assist] and Salesrepresentatives[Salesrep] should be inactive. Inactive relationships can be activated in measures by using USERELATIONSHIP.

Looking at your screenshot [Sales] has the value of sales assist. E.g. Jane has is assisting for 100 on 7th, while she is selling for 125. In the table on the right both measure returns 100. So either change which relationship is active, or change which measure is using the USERELATIONSHIP-function.

Btw, in terms of uploading files, do you see this option when you write a post:

[Mishutka]

Hi @sturlaws Phew, I figured it out in the end. I was reporting against the salespersons name from the Fact and not the Dimension table. Now it is working fine. Thanks so much, I will now try to use this logic in my real problem! 

Just reminds me always to supress dimension fields from showing in fact tables 🙂 

[I dont have the add attachments option, btw] 

[Mishutka]

Well, I guess I could use TREATAS without the relationship. I'm still kind of wondering how I force this. I feel there is something with Values or SUMX or Filter or something...