Total Not Showing in Table

[ysherriff]

Hi all,

I created a Datediff measure with conditional if statement, see image #1, that works like a charm but it doesn’t add the total when I put the measure in a table, see image #2, there is no total. What can i do in the measure so the total can be visible.

Image #1

Image # 2

Working file is also attached.

Thanks for your help.
PPD Prosepcts Report.pbix (27.1 MB)

[Harsh]

Hello @ysherriff,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve specified. Below is the measure alongwith the screenshot of the final results provided for the reference -

$ HubSpot Campaign RFP - Harsh =
VAR _Start_Day = 0

VAR _End_Day = 548

VAR _Date_Range_Condition =
FILTER( Deal ,
    DATEDIFF( Deal[RFP Received Date] , Deal[Campaign Member Create Date] , DAY ) >= _Start_Day &&
    DATEDIFF( Deal[RFP Received Date] , Deal[Campaign Member Create Date] , DAY ) <= _End_Day )

VAR _Results =
CALCULATE( SUM( Deal[RFP Amount] ) ,
    _Date_Range_Condition )

RETURN
_Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

PPD Prosepcts Report - Harsh.pbix (27.1 MB)

[ysherriff]

Thanks again Harsh luke always fast response

[Harsh]

Hello @ysherriff,

You’re Welcome!!!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

[ysherriff]

I forgot to ask. Could you kindly explain why your syntax worked and mine did not?

I want to learn. Much rhx

[Harsh]

Hello @ysherriff,

Here’s the explanation between the two measures -

In old measure, you had stated the condition as - “If date range is in between 0 and 548 then provide the results of RFP Amount or else 0”.

So what it actually did is, it provided you with the correct results at an individual line item level but at a grand total level since there was no presence of any context, it evaluated the results as 0. And that’s why, it didn’t provided the overall summation of results at a grand total level.

Similarly, you could have achieved the same results by creating a measure in order to fix the totals. Below is the measure provided for the reference -

Fix Totals = 
SUMX(
   ADDCOLUMNS(
      SUMMARIZE(
          Deal , 
          Deal[Yuhanna's Campaign Test] , 
          Deal[Deal Name] ) , 
      "@Totals" , 
      [$ HubSpot Campaign RFP] ) , 
   [@Totals] )

But what I did is, removed that IF statement since in this particular scenario, it was not required and substituted that same condition or logic inside the Filter function as an expression.

I’m providing few links below for the reference purposes where similar type of queries were raised in the past by the members on our forum and how the “IF()” function works when it’s context is misunderstood.

Hoping you find this helpful.

Thanks and Warm Regards,
Harsh

Wrong values on total

Hello @Harris, Thank You for posting your query onto the Forum. Well to fix the totals you’ll just be required to write a small additional formula. One of our expert @Greg has already created a post onto the forum which specifically addresses this type of issue. Below is the link of that post provided for the reference purpose. But when you write the formula you’ll see that how the context at the back-end is getting evaluated. On the visual you’re getting the results as “2.38%”. But at the ba…

Cumulative Total Giving Unexpected Results

Hello @Timmay, Thank You for posting your query onto the Forum. The reason why you’re getting the incorrect figure for “Cumulative Higher Sales” measure is because check the “Grand Total” of the “Higher Sales” measure which you’ve created. Although, the measure is providing correct results at an individual row level but the grand total result is incorrect i.e., it’s the same result as “Sales Total” measure. And to fix the grand totals issue, you’ll be required to write a small measure as prov…

Exclude Zero results in Overall Calculation?

Hello @gkavesh, Thank You for posting your query onto the Forum. Well just couple of days back I exactly addressed this same kind of issue onto the forum. Below is the link of that post provided for the reference. Please feel free to check out that post. Here’s your solution provieded below - (Let’s analyse this step-by-step) 1). The formula which you had used for “Productivity Rate” is as follows - Productivity Rate = DIVIDE( [Arrived - 1] , [Clinic Hours] , 0 ) Now, since you’ve complet…

Group By Division, IF Statement & Proper Subtotal Calculation

Hello @Helios, The reason why second formula is giving inflated results is because it is first considering the sum of each and every individual row level as per “VAR X” i.e., “COLD CALL = 256.90” and “EMAIL = 1,047.90” and then when you added the another condition i.e., “VAR FINAL”, it just summed up the results by 2x. So since previously you had the wrong total as “1,304.80”, now because of that additional variable which you added inside the same formula it inflated the results. It doesn’t m…

Nested If measures (DAX)

Hello @Ravi85, Yesterday, I addressed the very same issue onto the Forum. In order to fix the totals, you can write an additional measure which fixes the results for grand totals as well. One of our EDNA expert had already created a post on it pertaining to the topic - “How To Fix Incorrect Totals”. Below are the links of the post provided for the reference. Thanks and Warm Regards, Harsh

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[ysherriff]

@Harsh

VAR _Start_Day = 0

VAR _End_Day = 548

VAR _Date_Range_Condition =
FILTER( Deal ,
    DATEDIFF( Deal[RFP Received Date] , Deal[Campaign Member Create Date] , DAY ) >= _Start_Day &&
    DATEDIFF( Deal[RFP Received Date] , Deal[Campaign Member Create Date] , DAY ) <= _End_Day )

VAR _Results =
CALCULATE( SUM( Deal[RFP Amount] ) ,
    _Date_Range_Condition )

RETURN
_Results

[ysherriff]

Hi,

I wanted to get the average RFP Influence time so I used your example of Fix Totals and it works perfectly but how can I modify to exclude negative values?

Average Influence Time for Total = //includes negative values
AVERAGEX(
ADDCOLUMNS(
SUMMARIZE(
Deal,
Deal[Yuhanna’s Campaign Test],
Deal[Deal Name]),
“@Averages”,
[RFP Influence TIME]),
[@Averages])

[Harsh]

Hello @ysherriff,

Based on the measure that you’ve provided, I evaluated the results and exported them into an Excel file and nowhere I was able to see any negative values. The starting value that I see is itself from postive 1. Below is the screenshot provided for the reference -

Thanks and Warm Regards,
Harsh