cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reynaldo_malave
Helper I
Helper I

Counting based on a condition

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


WhatsApp Image 2021-01-06 at 16.29.27.jpeg

 

thanks 

 

Reynaldo

2 ACCEPTED SOLUTIONS
littlemojopuppy
Super User I
Super User I

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]
	)
)

 

 

View solution in original post

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]
	)
)

 

littlemojopuppy_0-1610037865799.png

 

View solution in original post

9 REPLIES 9
littlemojopuppy
Super User I
Super User I

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]
	)
)

 

 

View solution in original post

thanks! @littlemojopuppy 

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

@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. 

@reynaldo_malave No worries!  🙂

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

@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

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]
	)
)

 

littlemojopuppy_0-1610037865799.png

 

View solution in original post

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors