Incorrect Total from Measure Incorrect

[MDMC]

Hi, I'm new to DAX and PowerBI.  I have a fairly simple measure but cannot seem to get it to work.  Any help would be appreciated!

I am working in a live data model so I am limited to creating measures.  I have the following measure:

Active =

VAR TotalSpare = CALCULATE(SUM('table'[QuantityValue]), 'table'[Type] = 1 ||'table'[Type] = 3 

RETURN

IF([Recommended]<= TotalSpare, [Recommended], TotalSpare)

"Recommended" is another measure.  I cannot get the correct total, even though the line totals are correct.  At one point I added SUMX function prior to the IF statement, but got the same result.  

 

[JustJan · Accepted solution]

Hi @MDMC ,

The grand totals line is not calculated from the table above, but is recalculated at the grand total line. So the filter conditions for the measure calculation are different in that case. 

Try something like this: 

=IF(HASONEVALUE(Table[Category])
,[Active]
,SUMX(SUMMARIZE(VALUES(Table[Category]),Table[Category],"NumberOfActives",[Active]),[NumberOfActives])
)

basically it tells the measure to use your [Active] measure when one line level and when on the grand total line Summarize the values of [Active]. I referred to the first blurred column as Table[Category].

Hope this helps.

Jan 

if this is a solution for you, don't forget to mark it as such. thanks

[MDMC]

That worked!  Thank you so much!

[Mishutka]

Well, I am getting close.  The dax formula I have now is

=var Assister = MAX(Table1[Salesperson])
var MinDate = min(Table1[Date])
var MaxDate = max(Table1[Date])
return
if(HASONEFILTER(Table1[Salesperson]),

CALCULATE(SUM(Table1[Sales value]),all(Table1[Salesperson]),Table1[SalesAssist]=Assister,

Table1[Date]>=MinDate,Table1[Date]<=MaxDate),

CALCULATE(SUM(Table1[Sales value]),Table1[Date]>=MinDate,Table1[Date]<=MaxDate)

)

And this is almost right. The pivot table looks like this:

As you can see, the data for 8/1/20 shows 107 for Sales for Lawrie, and for Total but the Assister shows no sales for anyone, but a total of 107. This should be for John, but because he has no sales for the day, his assists dont show up. This is from the data table:

Date      Customer     Sales value    Salesperson         Sales Assist

07-Jan	Acme Subs	100	John	Jane
07-Jan	Widgets Inc	75	John	Lawrie
07-Jan	Acme Subs	125	Jane	John
07-Jan	Widgets Inc	175	Lawrie	John
08-Jan	Widgets Inc	107	Lawrie	John