Counting based on a condition

[reynaldo_malave]

Hello Devs,

Here is my problem. I have two measures. One for This Year total sales and the other for last year sales.  I have different stores and want to count the number of stores which have a negative difference.

sales TY =
     sum(
         'sales'[sales]
     )

sales LY =
     calculate(
          [sales TY],
          sameperiodlastyear(
               'date'[date_key]
          )
     )

diff =
     [sales TY]-[sales LY]

I tried using the following meassure 

Negative stores =

calculate(
     count('stores'[store_key]),
     filter(
          all('sales'),
          [diff]<0)
)

but i get the following result

thanks 

Reynaldo

[littlemojopuppy · Accepted solution]

Hi @reynaldo_malave .  Try this...

VAR	StoreSummary =
	SUMMARIZE(
		VALUES('stores'[store_key]),
		"CurrentYearSales",
		[Sales TY],
		"PriorYearSales",
		[Sales LY]
	)
RETURN

CALCULATE(
	COUNT('stores'[store_key]),
	FILTER(
		StoreSummary,
		[PriorYearSales] > [CurrentYearSales]
	)
)

[littlemojopuppy · Accepted solution]

Hi @reynaldo_malave .  Try this...

VAR	StoreSummary =
	SUMMARIZE(
		VALUES('stores'[store_key]),
		"CurrentYearSales",
		[Sales TY],
		"PriorYearSales",
		[Sales LY]
	)
RETURN

CALCULATE(
	COUNT('stores'[store_key]),
	FILTER(
		StoreSummary,
		[PriorYearSales] > [CurrentYearSales]
	)
)

[reynaldo_malave]

thanks! @littlemojopuppy 

[littlemojopuppy]

De nada!  Glad I could help.  Between us...surprised it's correct since I wrote it in Notepad.  😉

[reynaldo_malave]

@littlemojopuppy  hey man, sorry to bother like this but taking a better look at the measure i must say it did not work. It is just returning the total amount of stores in my stores table. I was so exited yesterday that got a number instead of an error or blank that I accept it as a solution and finish my days work. 

[littlemojopuppy]

@reynaldo_malave No worries!  🙂

Can you give me some raw data to work with and I'll figure this out

[reynaldo_malave]

@littlemojopuppy  you can download it from here. The correct answer should be 4. Thanks

https://drive.google.com/file/d/1VQNfOtFrIJu_maXjFqJeoxTiNz0X0R_3/view?usp=sharing

[littlemojopuppy · Accepted solution]

Hi @reynaldo_malave .  Remember when I said I was surprised it was correct because I wrote it in Notepad?  🙄

There were two issues.  First, the measure for [Sales TY] is returning all sales regardless of year (unless it's filtered by year).  The second is my measure left out the Store Key in the summary table variable.

Total Sales = SUM(Sales[sales])

Sales TY = 
    TOTALYTD(
        [Total Sales],
        'Calendar'[Date]
    )

Sales LY = 
    CALCULATE(
        [Sales TY],
        SAMEPERIODLASTYEAR('Calendar'[Date])
    )

-- Used to check the counts only
Sales YOY Change = [Sales TY] - [Sales LY] 

Negative Stores = 
VAR	StoreSummary =
	SUMMARIZE(
		VALUES('stores'[store_key]),
        Stores[store_key],
		"CurrentYearSales",
		[Sales TY],
		"PriorYearSales",
		[Sales LY]
	)
RETURN

CALCULATE(
	COUNT('stores'[store_key]),
	FILTER(
		StoreSummary,
		[PriorYearSales] > [CurrentYearSales]
	)
)

[reynaldo_malave]

@littlemojopuppy  You are a life saver. Thanks for taking the time. I did learn how to summarize. The hard way!

[littlemojopuppy]

@reynaldo_malave it's no trouble.  Wish I hadn't flubbed it yesterday 😑