Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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]
)
)
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]
)
)
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]
)
)
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 You are a life saver. Thanks for taking the time. I did learn how to summarize. The hard way!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |