topic Re: Calculate percentage with specific variables in DAX Commands and Tips

Calculate percentage with specific variables

Eddyns — Mon, 22 May 2023 08:17:14 GMT

Hello!

Im trying to calculate percentage sold items of ingoing stock since last time we recived new goods.

Department	Sold items	Date	Outgoing days since new stock	Ingoing Stock
Sweden	10	21.05.2023	1	100
Sweden	15	22.05.2023	2	90
Sweden	20	23.05.2023	3	75
Denmark	15	21.05.2023	1	100
Denmark	10	22.05.2023	2	85
Sweden	5	24.05.2023	1	95
Denmark	10	24.05.2023	1	100
Denmark	15	25.05.2023	2	90

Like the table shown, the data is something like that.

So I would like to calculate the percentage from department Denmark when the date is closest to today, and the "Outgoing days since new stock" is 1. So for Denmark in this example, it would show "25.05.2023" and a percentage of 15%.

Any ideas how to do this?

Re: Calculate percentage with specific variables

Eddyns — Mon, 22 May 2023 08:21:25 GMT

Dont know what happend to the table.

But "Outgoing days since new stock" has these values:

Outgoing days since new stock
1
2
3
1
2
1
1
2

Re: Calculate percentage with specific variables

Anonymous — Wed, 24 May 2023 02:27:55 GMT

Hi Eddyns ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

closest to today = ABS(DATEDIFF('Table'[Date],TODAY(),DAY))

(3)We can create a measure.

Measure = var _min=CALCULATE(MIN('Table'[closest to today]),FILTER(ALL('Table'),'Table'[Department]=MAX('Table'[Department]) && 'Table'[Outgoing days since new stock]=1)) var _date =CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Department]=MAX('Table'[Department]) && 'Table'[closest to today]=_min)) var _sold=CALCULATE(MAX('Table'[Sold items]),FILTER(ALL('Table'),'Table'[Department]=MAX('Table'[Department]) && 'Table'[Date]=_date)) var _stock=CALCULATE(MAX('Table'[Ingoing Stock]),FILTER(ALL('Table'),'Table'[Department]=MAX('Table'[Department]) && 'Table'[Date]=_date)) return DIVIDE(_sold,_stock,0)

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.