topic Re: Measures using filters of negative and positive results in DAX Commands and Tips

Measures using filters of negative and positive results

Marango — Thu, 12 Dec 2024 22:16:47 GMT

Hello,

would like to know how can I make measures with calculations but using filters. here is an example of what I pretend to do

I have the sales per country on 2023 and 2024. I am comparing them.

so I was thinking to make a measure to show only the countrys that has losing of sales . and another measure that let me show only the countries having an increase of sale with no return of the negative numbers. I think I need to make measures because at the end I need to make a comparative chart showing the losing sales and the increase of sales with the sales of previous year.

COUNTRY	2023	2024	Difference
USA	$5,000	$4,300	-$700
FRANCE	$5,000	$5,000	$0
GERMANY	$3,500	$2,000	-$1,500
MEXICO	$600	$1,200	$600
ITALY	$5,500	$5,300	-$200
CHINA	$5,000	$3,000	-$2,000
JAPAN	$5,000	$4,580	-$420
AUSTRALIA	$6,000	$6,100	$100
TOTAL	$35,600	$31,480	-$4,120

NEW MEASURES
Lost Sales			Increase of sales:
USA	-$700		MEXICO	$600
GERMANY	-$1,500		AUSTRALIA	$100
CHINA	-$2,000			$700
JAPAN	-$420
	-$4,620

FINAL CHART I NEED TO ACHIEVE

	2023	LOST SALE	INCREASE OF SALES
	$35,600	-$4,620	$700

thank you!

Re: Measures using filters of negative and positive results

powerbiexpert22 — Thu, 12 Dec 2024 23:11:38 GMT

Hi Marango ,

you can specify conditions in calculate which acts as filters , Calculate (function(), condition or filter)

example Calculate(sum(sales),Country="USA")

Re: Measures using filters of negative and positive results

Anonymous — Fri, 13 Dec 2024 06:12:04 GMT

Hi, Marango

Thanks for powerbiexpert22's reply. You can try this dax to achieve your need.

Lost Sale = CALCULATE(SUM('Table'[2023])- SUM('Table'[2024]),FILTER('Table','Table'[2023]>'Table'[2024])) Increase of sales = CALCULATE(SUM('Table'[2023])- SUM('Table'[2024]),FILTER('Table','Table'[2023]<='Table'[2024]))

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum