Forum Discussion

thmonte's avatar
thmonte
Icon for Helper IV rankHelper IV
8 years ago
Solved

Creating a value column for each time an item appears within a single date

I have a table that has the first 4 columns here.  I want to try and create a new column that breaks the "items' down to everytime they appear within a date range regardless of "Store".   so COUNT ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi thmonte,

     

    You could create a measure similar to:

    Column3 =
VAR myCount1 =
    IF (
        Table3[Region] IN { "UK", "US" },
        0,
        (
            CALCULATE (
                COUNTROWS ( 'Table3' ),
                FILTER (
                    ALLEXCEPT ( 'Table3', Table3[Date] ),
                    Table3[Item 1] = EARLIER ( 'Table3'[Item 1] )
                        && Table3[Region] <> "US"
                        && Table3[Region] <> "UK"
                )
            )
                + CALCULATE (
                    COUNTROWS ( 'Table3' ),
                    FILTER (
                        ALLEXCEPT ( 'Table3', Table3[Date] ),
                        Table3[Item 2] = EARLIER ( 'Table3'[Item 1] )
                            && Table3[Region] <> "US"
                            && Table3[Region] <> "UK"
                    )
                )
        )
    )
VAR myCount1percent =
    IF ( 'Table3'[Item 1] <> "null", DIVIDE ( 1 / myCount1, 1 ), 0 )
VAR myCount2 =
    IF (
        Table3[Region] IN { "UK", "US" },
        0,
        (
            CALCULATE (
                COUNTROWS ( 'Table3' ),
                FILTER (
                    ALLEXCEPT ( 'Table3', Table3[Date] ),
                    Table3[Item 1] = EARLIER ( 'Table3'[Item 2] )
                        && Table3[Region] <> "US"
                        && Table3[Region] <> "UK"
                )
            )
                + CALCULATE (
                    COUNTROWS ( 'Table3' ),
                    FILTER (
                        ALLEXCEPT ( 'Table3', Table3[Date] ),
                        Table3[Item 2] = EARLIER ( 'Table3'[Item 2] )
                            && Table3[Region] <> "US"
                            && Table3[Region] <> "UK"
                    )
                )
        )
    )
VAR myCount2percent =
    IF ( Table3[Item 2] <> "null", DIVIDE ( 1 / myCount2, 1 ), 0 )
RETURN
    myCount2percent + myCount1percent

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi thmonte,

 

I made a little modification to Phil_Seamark's solution to take the date into account. Please try:

Column = 

VAR myCount1 = CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 1] = EARLIER('Table3'[Item 1])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 2] = EARLIER('Table3'[Item 1])))
             
VAR myCount1percent = IF ('Table3'[Item 1] <> blank(), DIVIDE( 1 /   myCount1,1 ),0)

VAR myCount2 = CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 1] = EARLIER('Table3'[Item 2])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALLEXCEPT('Table3',Table3[Date]),Table3[Item 2] = EARLIER('Table3'[Item 2]))) 

VAR myCount2percent = IF(Table3[Item 2] <> BLANK(), DIVIDE( 1 / myCount2 , 1),0)

RETURN myCount2percent + myCount1percent

Replace ALL with ALLEXCEPT when calculating count values for each item.

 

Regards,
Yuliana Gu

  • thmonte's avatar
    thmonte
    Icon for Helper IV rankHelper IV
    8 years ago

    This looks really close but almost all my numbers are off by .5.

     

    In the simplest scenario item 1 is unique once and item 2 is unique once the expression should return 2.0.  In your example it is returning 1.5

     

    Edit: disregard, i believe there was just a syntax error

     

     

  • thmonte's avatar
    thmonte
    Icon for Helper IV rankHelper IV
    8 years ago

    This worked great!  Thank you for the response.  If I wanted to take this one step further and filter out regions that = US and UK and not have them get counted, would that be possible?

     

     

     

    DateRegionStoreItem 1Item 2ValueTotal
    11/12/2017USStore AItem AItem B 0.58
    11/12/2017USStore AItem AItem B 0.58
    11/12/2017UKStore AItem AItem C 0.75
    11/12/2017CAStore BItem Anull11
    11/12/2017CAStore CItem BItem C1 + 12
    11/12/2017CAStore CItem Dnull11
           
        Final Count 4

     

     

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi thmonte,

       

      You could create a measure similar to:

      Column3 =
VAR myCount1 =
    IF (
        Table3[Region] IN { "UK", "US" },
        0,
        (
            CALCULATE (
                COUNTROWS ( 'Table3' ),
                FILTER (
                    ALLEXCEPT ( 'Table3', Table3[Date] ),
                    Table3[Item 1] = EARLIER ( 'Table3'[Item 1] )
                        && Table3[Region] <> "US"
                        && Table3[Region] <> "UK"
                )
            )
                + CALCULATE (
                    COUNTROWS ( 'Table3' ),
                    FILTER (
                        ALLEXCEPT ( 'Table3', Table3[Date] ),
                        Table3[Item 2] = EARLIER ( 'Table3'[Item 1] )
                            && Table3[Region] <> "US"
                            && Table3[Region] <> "UK"
                    )
                )
        )
    )
VAR myCount1percent =
    IF ( 'Table3'[Item 1] <> "null", DIVIDE ( 1 / myCount1, 1 ), 0 )
VAR myCount2 =
    IF (
        Table3[Region] IN { "UK", "US" },
        0,
        (
            CALCULATE (
                COUNTROWS ( 'Table3' ),
                FILTER (
                    ALLEXCEPT ( 'Table3', Table3[Date] ),
                    Table3[Item 1] = EARLIER ( 'Table3'[Item 2] )
                        && Table3[Region] <> "US"
                        && Table3[Region] <> "UK"
                )
            )
                + CALCULATE (
                    COUNTROWS ( 'Table3' ),
                    FILTER (
                        ALLEXCEPT ( 'Table3', Table3[Date] ),
                        Table3[Item 2] = EARLIER ( 'Table3'[Item 2] )
                            && Table3[Region] <> "US"
                            && Table3[Region] <> "UK"
                    )
                )
        )
    )
VAR myCount2percent =
    IF ( Table3[Item 2] <> "null", DIVIDE ( 1 / myCount2, 1 ), 0 )
RETURN
    myCount2percent + myCount1percent

       

      Best regards,

      Yuliana Gu