Forum Discussion

Matthew_Theis's avatar
Matthew_Theis
Advocate II
7 years ago

Distinct Count using an indirect filter

Hello Everyone,  

 

I'm building a gauge chart to show the percent of quotes that a quoter submits before 24 hours passes.  I am able to calculate this metric, but I'm having trouble producing the target value.  
From the tables below, you can see that the calculation is working, and there is a relationship between VP Area and the Quoter.

 

What I want to see happen, is that when a Quoter is selected from a Report Filter, I expect to see the Quoter Percentile as the Value, and the VP Area Percentile as the target.  

 

Example: Camilo Perez is selected.  Value 61.6.  Target 64.0.

 

What's happening is the VP Area Percenile filters along with the selected Quoter.

 

Does it matter that VP area comes from a different table?

 

Speedometer Percentile Quotes < 24 Hours =
VAR TotalQuotes =
CALCULATE(
DISTINCTCOUNT(QuotesHeader[Id])
)
Return

DIVIDE(
CALCULATE(
DISTINCTCOUNT(QuotesHeader[Id]),
FILTER(QuotesHeader,QuotesHeader[Submitted_Duration]<86400000)
),
TotalQuotes,0
)*100
***********************************************************************************************************
Speedometer VP Area Percentile Quotes < 24 Hours =
VAR TotalQuotes =
CALCULATE(
DISTINCTCOUNT(QuotesHeader[Id])
)
Return

(CALCULATE(
DISTINCTCOUNT(QuotesHeader[Id]),
FILTER(QuotesHeader,QuotesHeader[Submitted_Duration]<86400000),
ALL(QuotesHeader[Assignee Clean])
)/TotalQuotes)*100
Need Help

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    Matthew_Theis,

     

    You may try the measure below.

    Speedometer VP Area Percentile Quotes < 24 Hours =
VAR TotalQuotes =
    CALCULATE (
        DISTINCTCOUNT ( QuotesHeader[Id] ),
        ALL ( QuotesHeader[Assignee Clean] )
    )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( QuotesHeader[Id] ),
            QuotesHeader[Submitted_Duration] < 86400000,
            ALL ( QuotesHeader[Assignee Clean] )
        ),
        TotalQuotes
    )
        * 100
    • Matthew_Theis's avatar
      Matthew_Theis
      Advocate II
      7 years ago

      Hi v-chuncz-msft,

       

      I tried the mentioned calculation, which appears to be correct on the surface.  When I filter on an Assignee Clean, your calculation filters.  What I expect to see is 64.0 for the Americas, but when I filter on Camilo Perez, the calculation seems to use only the records associated with Camilo, thus returning a score of 61.4.

       

      Thanks!

       

      Matthew