Count of measure

[Anonymous]

Hi Guys,

Some help on this issue would be greatly appreciated!

The below Matrix indicates if a Store have inventory of a Product during three consecutive Months. Then, last two columns indicate if a Product have dropped/gain inventory from one Month to another.

I would like two measures:

• One measure that calculates the Count of ↑ 
• One measure that calculates the Count of ↓

Is this possible?

Here is how I calculated my other measures:

Month 1 vs Month 2 = IF ( [Month 1] <> BLANK() && [Month 2] = [Month 1] , "↔" , IF( [Month 3] = "Yes" && [Month 2] = "No" , "↓" , IF( [Month 1] = "No" && [Month 2] = "Yes" , "↑")))

Month 2 = CALCULATE( FIRSTNONBLANK( 'Products Table'[Answer],0), 'Products Table'[MonthNumber] = MONTH(TODAY())-1)

Month 1 = CALCULATE( FIRSTNONBLANK( 'Products Table'[Answer],0), 'Products Table'[MonthNumber] = MONTH(TODAY())-2)

Here is an extract of the data:

Store name	Product	MonthNumber	Month	Answer
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Fill and Go Replacement Filter and Cap 1pk	7	July	Yes
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Fill and Go Replacement Filter and Cap 1pk	6	June	Yes
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Fill and Go Replacement Filter and Cap 1pk	5	May	Yes
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Filter Cartridge 1pk	6	June	No
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Filter Cartridge 1pk	7	July	Yes
FOODLAND IGA HOPE VALLEY DRAKES	Brita Classic Filter Cartridge 1pk	5	May	Yes
FOODLAND IGA HOPE VALLEY DRAKES	Brita Fill and Go Active Blue Water Bottle 600ml	7	July	No
FOODLAND IGA HOPE VALLEY DRAKES	Brita Fill and Go Active Blue Water Bottle 600ml	6	June	No
FOODLAND IGA HOPE VALLEY DRAKES	Brita Fill and Go Active Blue Water Bottle 600ml	5	May	No

Please note: My data contains many stores.

Thanks in advance.

Robin

[v-jiascu-msft · Accepted solution]

Hi Robin,

You may try  SUMMARIZE Function  like below.

Total Count of ↑ =
VAR Mon1vMon2In =
    CALCULATE (
        SUMX (
            SUMMARIZE (
                'Products Table',
                'Products Table'[Product],
                "a", [Month 1 vs Month 2]
            ),
            IF ( [a] = "↑", 1, 0 )
        )
    )
RETURN
    VAR Mon2vMon3In =
        CALCULATE (
            SUMX (
                SUMMARIZE (
                    'Products Table',
                    'Products Table'[Product],
                    "a", [Month 2 vs Month 3]
                ),
                IF ( [a] = "↑", 1, 0 )
            )
        )
    RETURN
        Mon1vMon2In + Mon2vMon3In

Total Count of ↓ =
VAR Mon1vMon2De =
    CALCULATE (
        SUMX (
            SUMMARIZE (
                'Products Table',
                'Products Table'[Product],
                "a", [Month 1 vs Month 2]
            ),
            IF ( [a] = "↓", 1, 0 )
        )
    )
RETURN
    VAR Mon2vMon3De =
        CALCULATE (
            SUMX (
                SUMMARIZE (
                    'Products Table',
                    'Products Table'[Product],
                    "a", [Month 2 vs Month 3]
                ),
                IF ( [a] = "↓", 1, 0 )
            )
        )
    RETURN
        Mon1vMon2De + Mon2vMon3De

Best Regards,

Dale

[Anonymous]

Hi Dale,

Thanks a lot for your time, I really appreciate.

Your formula works perfectly for the mentioned visualisation.

However, ideally I would like to use it in the below visualisation (just with product column), and it's not giving me a correct count...

Would you know how to fix this please?

Robin

[v-jiascu-msft]

Hi Robin,

Please add other measures into the table to check if these 0s are the answer.  If so, I'm afraid there could be something wrong with other parts. Can you share the pbix file and expected output please? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Dale

[Anonymous]

All good I got it! I was playing around with the SUMMARIZE function you sent and I manage to get the correct answer:

I replaced 

'Products Table'[Product] 

by

'Products Table'[Store]

Many thanks again

Robin

[Ashish_Mathur]

Hi,

So if you select a certain month in the slicer, say July, do you want to know which products increased over the previous month and which decreased over the previous month?  If my understanding is correct, then the answer will be 1 or 0.  Am i correct?