cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Count of measure

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.

Robin

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.