Forum Discussion

reynaldo_malave's avatar
reynaldo_malave
Helper III
5 years ago
Solved

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


 

thanks 

 

Reynaldo

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    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's avatar
    littlemojopuppy
    5 years ago

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

     

     

9 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

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