NEED HELP! Grand Total is not correct for first half of measure

[hello789567]

Hey,

I am having an issue with the grand total of my measure not calculating correctly. When I break down my measure to show the VAR "ReferralPartner" results only (The green box in the picture) PowerBi totals everything correctly. Even checked it against SalesForce. The issue I have is when testing the first half of my measure VAR "ResellerFiltered" (The red box in the picture). The row and row totals are correct but the Column/Grand Totals at the bottom are wrong by about 5.5k. The only difference in the 2 variables is that the first has filter 3 and not filter 4, and the bottom has 4 and not 3.

Any one know why it works for one and not the other?

[Greg_Deckler · Accepted solution]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[hello789567]

I have a lot of measures that are like this, built off of Salesforce logic, and this has never been an issue before. I understand that the totals row doesn't have a unique value, but I built this so that it can only give me the numbers that apply. Just dont understand why this technique has always worked before and is not now. I have looked at those post and a lot of others and I can't seem to write a formula that fixes it. Do you have a suggested one to try? I'm struggling.

[Greg_Deckler]

Well, it just depends on your measure calculation. Certain types of calculations will run into this issue and certain others will not. If you could post your DAX formulas I can probably help you identify what exactly is causing the issue.

[hello789567]

Converted MRR =

VAR rates = CALCULATE(FIRSTNONBLANK('Dated Conversion Rate'[ConversionRate],1), FILTER('Dated Conversion Rate',

'Dated Conversion Rate'[StartDate] <= MIN(Opportunity[CloseDate]) &&

'Dated Conversion Rate'[NextStartDate] > MIN(Opportunity[CloseDate]) )

)

VAR GBPtoUSD = CALCULATE( SUMX(Opportunity,DIVIDE(Opportunity[Net_Monthly_Revenue__c],rates)),

Opportunity[CurrencyIsoCode] = "GBP" )

VAR USD = CALCULATE( SUMX(Opportunity,Opportunity[Net_Monthly_Revenue__c]),

Opportunity[CurrencyIsoCode] = "USD" )

RETURN

GBPtoUSD + USD

----------------------------------------------------------------------------------------------------
Reseller/Referral Pipeline MRR = 

VAR ResellerFiltered = CALCULATE([Converted MRR],

Opportunity[StageName] = "Suspect" // Filter 1

|| Opportunity[StageName] = "Qualified Lead"

|| Opportunity[StageName] = "Discovery"

|| Opportunity[StageName] = "Demo"

|| Opportunity[StageName] = "Confirmed Value"

|| Opportunity[StageName] = "Finalist"

|| Opportunity[StageName] = "Contract"

|| Opportunity[StageName] = "Accounting Closed Won"

|| Opportunity[StageName] = "Closed/Won"

|| Opportunity[StageName] = "Sales Closed Won",

Opportunity[Net_Monthly_Revenue__c] >= 0 , //Filter 2

Opportunity[Reseller (Hootsuite/ATT/Voda)] = "Yes" , //Filter 3

Opportunity[Referral_Partner_and_or_Reseller__c] = "Yes" //Filter 5

)

VAR ReferralFiltered = CALCULATE([Converted MRR],

Opportunity[StageName] = "Suspect" // Filter 1

|| Opportunity[StageName] = "Qualified Lead"

|| Opportunity[StageName] = "Discovery"

|| Opportunity[StageName] = "Demo"

|| Opportunity[StageName] = "Confirmed Value"

|| Opportunity[StageName] = "Finalist"

|| Opportunity[StageName] = "Contract"

|| Opportunity[StageName] = "Accounting Closed Won"

|| Opportunity[StageName] = "Closed/Won"

|| Opportunity[StageName] = "Sales Closed Won",

Opportunity[Net_Monthly_Revenue__c] >= 0 , //Filter 2

Opportunity[Referred_By__c] <> BLANK() , //Filter 4

Opportunity[Referral_Partner_and_or_Reseller__c] = "Yes" //Filter 5

)

RETURN

ResellerFiltered + ReferralFiltered

[Greg_Deckler]

Right, so the issue is most likely in your rates VAR. In the context of ALL (your total line) your MIN is going to return the MIN of all [CloseDate] which I am guessing is not what you want. That's one possibility. Is Converted MRR a measure? Tough to know exactly without sample data to play around with. But, just remember that in the Total line, measures calculate in the context of ALL. That is why measure totals often end up being "wrong".

[hello789567]

Converted MRR is a measure. If it is the MIN( 'opportunity'[CloseDate] ) that is causing the issue, is there another way to phrase it? It wouldn't let me just put ('opportunity'[CloseDate]). That measure converts the (Opportunity[Net_Monthly_Revenue__c]) based on the ('opportunity'[CloseDate]) falling between dates, and retreaving the conversion rate. table below is the Conversion Rate table.

I also wonder why it would not mess with the lower half of the measure. ( VAR ReferralFiltered ). I also have the following measure that references the Converted MRR measure and its spot on as well.

Pipeline MRR =

VAR SalesFiltered = CALCULATE([Converted MRR],

Opportunity[StageName] = "Suspect" // Filter 1

|| Opportunity[StageName] = "Qualified Lead"

|| Opportunity[StageName] = "Discovery"

|| Opportunity[StageName] = "Demo"

|| Opportunity[StageName] = "Confirmed Value"

|| Opportunity[StageName] = "Finalist"

|| Opportunity[StageName] = "Contract"

|| Opportunity[StageName] = "Accounting Closed Won"

|| Opportunity[StageName] = "Closed/Won"

|| Opportunity[StageName] = "Sales Closed Won" ,

Opportunity[Type] = "Reseller - Upgrade / Reneg" //Filter 2

|| Opportunity[Type] = "New Business"

|| Opportunity[Type] = "Upgrade"

|| Opportunity[Type] = "Upgrade and Contract Re-Negotiation"

|| Opportunity[Type] = "Add-On - Branch/Domain"

|| Opportunity[Type] = "Contract Re-Negotiation - NO Service Chg"

|| Opportunity[Type] = "Reseller" ,

Opportunity[Net_Monthly_Revenue__c] >= 0 , //Filter 3

SEARCH("Mortgage" ,Opportunity[Owner_Role__c], , 999) <> 999 //Filter 4

|| SEARCH("Enterprise" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("EMEA" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("New Logo" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("Expansion" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("Inside Sales" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("Public" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("Channel Sales" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("Government Team" ,Opportunity[Owner_Role__c], , 999) <> 999,

Opportunity[ResellerOpp_] = 0

//SEARCH("ResellerOpp_" ,Opportunity[Name], , 999) = 999 //Had to add the opposite of Filter 5 due to duplicates within Sales/Reseller logic )

VAR ResellerFiltered = CALCULATE([Converted MRR],

Opportunity[StageName] = "Suspect" // Filter 1

|| Opportunity[StageName] = "Qualified Lead"

|| Opportunity[StageName] = "Discovery"

|| Opportunity[StageName] = "Demo"

|| Opportunity[StageName] = "Confirmed Value"

|| Opportunity[StageName] = "Finalist"

|| Opportunity[StageName] = "Contract"

|| Opportunity[StageName] = "Accounting Closed Won"

|| Opportunity[StageName] = "Closed/Won"

|| Opportunity[StageName] = "Sales Closed Won" ,

Opportunity[Net_Monthly_Revenue__c] >= 0 , //Filter 3

Opportunity[ResellerOpp_] = 1)

VAR CSMFiltered = CALCULATE([Converted MRR],

Opportunity[Type] = "Reseller - Upgrade / Reneg" //Filter 2

|| Opportunity[Type] = "New Business"

|| Opportunity[Type] = "Upgrade"

|| Opportunity[Type] = "Upgrade and Contract Re-Negotiation"

|| Opportunity[Type] = "Add-On - Branch/Domain"

|| Opportunity[Type] = "Contract Re-Negotiation - NO Service Chg"

|| Opportunity[Type] = "Reseller" ,

Opportunity[Net_Monthly_Revenue__c] >= 0 , //Filter 3

Opportunity[StageName] = "Accounting Closed Won" , // Filter 6

SEARCH("accounting" ,Opportunity[Owner_Role__c], , 999) <> 999 //Filter 7

|| SEARCH("customer" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("CSM" ,Opportunity[Owner_Role__c], , 999) <> 999

|| SEARCH("CSx" ,Opportunity[Owner_Role__c], , 999) <> 999

)

RETURN

SalesFiltered + ResellerFiltered + CSMFiltered

[Greg_Deckler]

Tough to say without the data if there is another way to phrase it. Quite probably no. But, the technique in Measure Totals the Final Word should work.

[hello789567]

Looks like you are 100% spot on about the MIN(closedate). I filtered by USD which doesnt convert and the total matched up. So it seems my large measure is fine and handles total but my Converted MRR doesn't. 

Seriously Thank you so much!!!! I will have to find a new way to convert the MRR.

I have never used the Summarize function and struggled to put it into context for my situation and data, but I figured it out thanks to you!

IF(HASONEVALUE(Opportunity[Name]),

ResellerFiltered+ReferralFiltered,

SUMX(SUMMARIZE(Opportunity,Opportunity[Name],

Opportunity[CloseDate],

"_name", [Converted MRR],

"_date",[Converted MRR] )

,[_date]

)

)