Mismatch in callout and target value of tachometer with no filters

[dc189]

Hello,

With no filters applied my tachometer is supposed to show exactly same values for callout value and target value but I'm seeing different values as below:

74% (callout value/promotion %); 73% (target value/national avg)

The formulae I've used are:

Callout value: Promotion % = Table[Promotional Sales]/([Promotional Sales]+[Non-promotional Sales])..........(I also used with CALCULATE but still getting the error)

Target value: National Avg = CALCULATE([Promotion %],ALLEXCEPT(Table,Table[Year]))

If someone could help me understand why the callout and target values are not equal when no filters are applied, that would be great.

Thank you,

dc189

[Willson_Yuan]

Hi @dc189,

Generally, it is supposed to show exactly same values for callout value and target value with no filters applied to your tachometer. However, if you apply format for your [Promotional Sales],[Non-promotional Sales] and [Promotion %], you may have truncated the decimal values. Especially for target value, as it group as years first, the value for target value may be smaller than callout value.

[dc189]

Hello @Willson_Yuan,

I removed the format I had applied to [Promotional Sales], [Non-promotional Sales] [Promotion %] and [National Avg] and switched them back to general (or default) format as below.

However, I'm still seeing different values in the Tachometer (74% in callout value and 73% in target value with no filters applied). Please let me know what I'm missing.

Also, when I filter any year from 2010 through 2013, the values match however for 2014, there is again a mismatch.

Thanks,

dc189

[Willson_Yuan]

Hi @dc189 ,

In this scenario, please try to create [Promotional Sales CAL], [Non-promotional Sales CAL] and [Promotion % CAL] as below:

[Promotional Sales CAL] = CALCULATE([Promotional Sales],ALLEXCEPT(Table,Table[Year]))
[Non-promotional Sales CAL] = CALCULATE([Non-promotional Sales],ALLEXCEPT(Table,Table[Year]))
[Promotion % CAL] = [Promotional Sales CAL]/([Promotional Sales CAL]+[Non-promotional Sales CAL])

If this issue still exists, to determine the root casue,  please share your PBIX file .

[dc189]

Hi @Willson_Yuan,

I used your solution and it shows the matching values when nothing is filtered, but now the values don't change when I apply filters other than Year. Please see dropbox link to a sample pbix file: https://www.dropbox.com/s/v94sxvgmc45zp9e/Book%201.pbix?dl=0

If I filter for state of CA, I the needle doesn't move at all from 73% (which is the target and callout value when nothing is filtered). It should move, because the Promotion % for CA is actually 88% (much above national average of 73%).

So ultimately what I want is: the target and callout values should match when I either filter for nothing or I filter for any one year. However, the callout value should be different from target value if I filter for a region or state (or it could be same if the value for that region or state is exactly same as national average, but I'm sure you get my point).

Let me know if you need more info. Thanks.

[Willson_Yuan · Accepted solution]

Hi @dc189,

As I test in your sample pbix file, below DAX formula works properly for me:

National Avg =
CALCULATE (
    (
        CALCULATE (
            SUM ( Sheet2[Sales] ),
            FILTER ( Sheet2, Sheet2[Promotion Type] = "Promotion" )
        )
            / SUM ( Sheet2[Sales] )
    ),
    ALLEXCEPT ( Sheet2, Sheet2[Year] )
)

Please try below DAX formula:

National Avg =
CALCULATE (
    (
        CALCULATE (
            SUM ( Table[Sales] ),
            FILTER ( Table, Table[Promotion Type] = "Promotion" )
        )
            / SUM ( Table[Sales] )
    ),
    ALLEXCEPT ( Table, Table[Year] )
)

[dc189]

Hi @Willson_Yuan,

Sorry about the late reply. I was out for the weekend.

I tried your new National Avg formula and it works just as well as the old one. But that is not the issue. The issue is with the target value or "Promotional % CAL". Please see two images below.

    

The first image to the left shows 74% for the year 2014 with no state or region filtered. But the second image to the right still shows 74% for the year 2014, with the state of CA filtered. I know that is wrong because the figure for CA is actually 88% (much higher than the average of 74%). So how do I get the 88%? What formula should I use for target value?

Thanks,

dc189

[Willson_Yuan]

Hi @dc189 ,

Thanks for your response.

As i test in your PBIX file, your initial callout and target value worked as your desired.

Callout value: Promotion % = [Promotional Sales]/([Promotional Sales]+[Non-promotional Sales])
Target value: National Avg = CALCULATE([Promotion %],ALLEXCEPT(Table,Table[Year]))

[dc189]

Hi @Willson_Yuan,

Thanks for your response. I realized I had applied a filter that wasn't included in the National Avg formula and the results are fine now. Based on your suggestions so far, I used the following. The key was your National Average formula, where you used CALCULATE on source columns (Sales and Promotion Type) rather than Promotion %.

Promotion % = CALCULATE (
SUM ( Table[Sales] ),
FILTER ( Table, Table[Promotion Type] = "Promotion" )
) / SUM ( Table[Sales] )

National Avg = CALCULATE (

    (CALCULATE (

            SUM ( Table[Sales] ),

            FILTER ( Table, Table[Promotion Type] = "Promotion" ))

            / SUM ( Table[Sales] )

    ),

    ALLEXCEPT ( Table, Table[Year] )

)

Thank you so much for your help!

dc189