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 how many times a distinct item appears in "Item 1" OR Item 2" within a single day and divide it by 1 to give it a value. Then once each distinct item has a value add the two for that specific row to come up with the "Total" column. DateStoreItem 1Item 2ValueTotal11/12/2017Store AItem AItem B.25 + .330.5811/12/2017Store AItem AItem B.25 + .330.5811/12/2017Store AItem AItem C.25 + .500.7511/12/2017Store BItem Anull.250.2511/12/2017Store CItem BItem C.33 + .500.8311/12/2017Store CItem Dnull11 3.99 I wasn't really sure what to research on this as I feel its a pretty unique case. Any help would be appreciated.

Helper 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 how many times a distinct item appears in "Item 1" OR Item 2" within a single day and divide it by 1 to give it a value. Then once each distinct item has a value add the two for that specific row to come up with the "Total" column.

Date	Store	Item 1	Item 2	Value	Total
11/12/2017	Store A	Item A	Item B	.25 + .33	0.58
11/12/2017	Store A	Item A	Item B	.25 + .33	0.58
11/12/2017	Store A	Item A	Item C	.25 + .50	0.75
11/12/2017	Store B	Item A	null	.25	0.25
11/12/2017	Store C	Item B	Item C	.33 + .50	0.83
11/12/2017	Store C	Item D	null	1	1
					3.99

I wasn't really sure what to research on this as I feel its a pretty unique case. Any help would be appreciated.

Need Help

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

5 Replies

Phil_Seamark

Microsoft Employee

8 years ago

HI thmonte

This is close and works on your sample data. It probably needs some tweaking around the use of the ALL in the FITLER functions but that should be easy enough.

Column = 

VAR myCount1 = CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 1] = EARLIER('Table3'[Item 1])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),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(ALL('Table3'),Table3[Item 1] = EARLIER('Table3'[Item 2])))
             + CALCULATE(COUNTROWS('Table3'),FILTER(ALL('Table3'),Table3[Item 2] = EARLIER('Table3'[Item 2]))) 

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

RETURN myCount2percent + myCount1percent

v-yulgu-msft

Microsoft 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
Helper 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

Helper 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?

Date	Region	Store	Item 1	Item 2	Value	Total
11/12/2017	US	Store A	Item A	Item B		0.58
11/12/2017	US	Store A	Item A	Item B		0.58
11/12/2017	UK	Store A	Item A	Item C		0.75
11/12/2017	CA	Store B	Item A	null	1	1
11/12/2017	CA	Store C	Item B	Item C	1 + 1	2
11/12/2017	CA	Store C	Item D	null	1	1

				Final Count		4

v-yulgu-msft

Microsoft 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

Forum Discussion

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

5 Replies

Related content

Column created in a table appears in another table

Create columns group

Report not appearing in app

"A backup was created of you Power BI report" message appearing

Cannot create Eventstream in Microsoft Fabric trial — blank dialog appears