Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
2 years ago

Looking for help with measures please

Hi, I’m looking for a few measures to get data from this table please. I need to find:

 

Row A – Total Numbers

Per customer ID, the number of customers whose score has Increased from the earliest score to the latest score by date

Per customer ID, the number of customers whose score has Decreased from the earliest score to the latest score by date

Per customer Id, the number of customers whose score has Score Remained Same / only had 1 score

 

Row B - % from the Row A results

Per customer ID, the % of the total customer ID’s who have Increased in score by date

Per customer ID, the % of the total customer ID’s who have Decreased in score by date

Per customer ID, the % of the total customer ID’s whose score has Score Remained Same / only had 1 score

 

Row C

The average amount of appointments the customer had per Increased Score

The average amount of appointments the customer had per Decreased Score

The average amount of appointments the customer had whose Score Score Remained Same / only had 1 score

 

If it could look something like the screenshot that would be amazing:. Thanks in advance!

 

 

DateAppointment NumberCustomer IDCustomer nameScore
May-231A1Dave Davely45
Jun-232A1Dave Davely33
Jul-233A1Dave Davely24
Aug-234A1Dave Davely34
Sep-235A1Dave Davely55
May-236B2John Johnson60
Jun-237B2John Johnson50
Jul-238B2John Johnson40
May-239C3Evan Evans45
Jun-2310C3Evan Evans70
Jul-2311C3Evan Evans80
Aug-2312C3Evan Evans90
May-2313D4Adam Adams43
Jun-2314D4Adam Adams23
Jul-2315D4Adam Adams33
Aug-2316D4Adam Adams45
May-2317E5Ben Benson66
Jun-2318E5Ben Benson77
Jul-2319E5Ben Benson88
Aug-2320E5Ben Benson99
May-2321F6Carly Carlson50
May-2322G7Emily Elmson60
Need Help
Tips and Tricks

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    RichOB Let's see:

    Total Icreased Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                DISTINCT( 'Table'[Customer ID] ),
                "__FirstDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] )
                RETURN
                    __FirstScore,
                "__LastDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] )
                RETURN
                    __LastScore
            ),
            "__Diff", [__LastDateScore] - [__FirstDateScore]
        )
    VAR __Result = COUNTROWS( FILTER( __Table, [__Diff] > 0 ) )
RETURN
    __Result



Total Percent Increased Measure = DIVIDE( [Total Icreased Measure], COUNTROWS(DISTINCT('Table'[Customer ID])), 0)




Average Appointments Increased Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                DISTINCT( 'Table'[Customer ID] ),
                "__FirstDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] )
                RETURN
                    __FirstScore,
                "__LastDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] )
                RETURN
                    __LastScore
            ),
            "__Diff", [__LastDateScore] - [__FirstDateScore]
        )
    VAR __IncreasedCustomers = DISTINCT( SELECTCOLUMNS( FILTER( __Table, [__Diff] > 0 ), "__ID", [Customer ID] ) )
    VAR __Result = AVERAGEX( SUMMARIZE( FILTER( 'Table', [Customer ID] IN __IncreasedCustomers ), [Customer ID], "__Count", COUNTROWS('Table') ), [__Count] )
RETURN
    __Result
    • RichOB's avatar
      RichOB
      Icon for Post Partisan rankPost Partisan
      2 years ago

      Hi Greg_Deckler thanks for your reply!

       

      I've got the increased column correct, as well as the total clients decreased and avg appointments decreased, thank you.

       

      I'm having trouble figuring out the:

      - Total Percent Decreased measure

       - One meeting /No Change Total, Percent, and Avg Appointment

       

      I think the calculations for the above are what's stumping me, would you be able to help with those as well please?

       

      Thanks

      Rich

       

       

  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    2 years ago

    Hi Greg_Deckler Thanks for your help with this, all looks great!

     

    Sorry to be a pain but I now need to discount people who only had one score. Would you be so kind to change the "One Score/No Change" to "No Change" for people who had 2 or more scores with no change in their first to last scores.

     

    Then a measure (or way) to rule out scores for people who only had 1 score, please?

     

    Sorry again and thank you!!

     

    Rich

     

    Would you be so kind to change the combined "One Meeting/No Change" measures to have just the No Change in score please? 

     

     

    Thanks so much for your help

     

    Rich

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      RichOB That's a pretty trivial change. The updated pbix has those measures in in. Basically only one line has to change:

      Total No Change Measure 2 = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                FILTER( SUMMARIZE( 'Table', [Customer ID], "__Count", COUNTROWS( 'Table' ) ), [__Count] > 1 ),
                "__FirstDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] )
                RETURN
                    __FirstScore,
                "__LastDateScore",
                    VAR __CustomerID = [Customer ID]
                    VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] )
                    VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] )
                RETURN
                    __LastScore
            ),
            "__Diff", [__LastDateScore] - [__FirstDateScore]
        )
    VAR __Result = COUNTROWS( FILTER( __Table, [__Diff] = 0 ) )
RETURN
    __Result

      This is the line that excludes customers with only 1 row.

      FILTER( SUMMARIZE( 'Table', [Customer ID], "__Count", COUNTROWS( 'Table' ) ), [__Count] > 1 ),
       
  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    2 years ago

    Greg_Deckler Ah right that makes sense with the filter thank you. Something isn't right when I replicate the measures to my real-life scenario. 

     

    (all personal info removed) - Score Test - Google Sheets

     

    The total number of people is correct but the percentages don't add up to 100 which makes me think they're not taking into consideration every result or date? This is what I'm getting:

     

     

    If you wouldn't mind having a look I'd be very grateful.

     

    Thanks

    Rich

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      RichOB I suspect it is because you are counting the people with only 1 appointment in your total. So to avoid that, probably this:

      Total Percent Increased Measure = 
  DIVIDE( 
    [Total Icreased Measure], 
    COUNTROWS(FILTER( SUMMARIZE( 'Table', [Customer ID], "__Count", COUNTROWS( 'Table' ) ), [__Count] > 1 )), 
    0
  )