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 ...
Need Help
Tips and Tricks
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

 

 

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

    RichOB Was just working on this to add to the Quick Measures Gallery. The attached PBIX (below sig) has all of the requested measures I believe. It really just comes down to change the > sign to < or =