Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reynaldo_malave
Helper III
Helper III

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

 

 

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

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.