topic Re: Divide the value of a dynamic column in DAX Commands and Tips

Divide the value of a dynamic column

jmgarcia34 — Tue, 19 Apr 2022 13:56:10 GMT

Hi,

My name is Jose and im new with power BI:

I have this:

The "QTY" is adjusted according to the dates we choose in the date selector, so it is not a fixed number.

I want to DIVIDE this number by 4 in a column but with this formula take the QTY in total, no in the date selected

YKU = SUM(postedsalesinvoiceSalesInvLines[Quantity])/4

Is there any way to select the column to make a new column that divides the values of the QTY COLUMN by 4?

Thanks!

Re: Divide the value of a dynamic column

tamerj1 — Tue, 19 Apr 2022 14:32:45 GMT

Hi jmgarcia34

when you say "I want to DIVIDE this number by 4 in a column but with this formula take the QTY in total, no in the date selected" is this date in same table or you have a date table?

Re: Divide the value of a dynamic column

jmgarcia34 — Tue, 19 Apr 2022 14:39:11 GMT

Hi and thanks in advance 😄

The date is in the same table (image below):

The idea is easy in excel:

A column that gives as result:

Qty/4

*Qty is variable depending on the selected date.

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 04:02:00 GMT

jmgarcia34
Then your formula YKU = SUM(postedsalesinvoiceSalesInvLines[Quantity])/4 is correct. What results are you getting if not correct?

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 06:49:00 GMT

The date is in the same table, i send the image in one message above

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 07:00:53 GMT

@jmgarcia34

you replied to the old measage

please reply to the following:
Then your formula YKU = SUM(postedsalesinvoiceSalesInvLines[Quantity])/4 is correct. What results are you getting if not correct?

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 08:00:38 GMT

The results that this formula gives me are taken from:
"Quantity" Total since 2020.

It does not take the number shown in the table only on the dates we require.

We would like that division to be done in relation to the date and quantity shown in the table, since depending on the date..... that number varies

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 08:36:10 GMT

jmgarcia34
What is the X-Axis of this chart? Is it Date?

Were shall the new measure fit in the visual? In the same table/chart? or In separate card? Why do you say that SUM(postedsalesinvoiceSalesInvLines[Quantity]) shall ignore the dates in the visual?! Are you sure you are creating a Measure not a column?

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 08:45:26 GMT

Hello again, first of all thank you very much for your attention, really 😄

- yes, the x-axis is the date and the y-axis is the quantity (in the graph you see the orders of materials according to the selected dates and in the table you see the total)
- The new measure I would like to have it as a column only without affecting the graph (it is only to have a stock forecast).
- Yes, I am creating a measure.

Example:

The total quantity YKSESE-150G on the selected dates in the image is 10464, I would like to create the column (divided by 4) and have the result be 2616.

If I change the date range and the total Qty is 8000, the column I want to create should show 2000.

The problem is that if I put "SUM(postedsalesinvoiceSalesInvLines[Quantity])" what I said before happens, it takes the total Qty since 2020.

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 08:51:41 GMT

jmgarcia34

If the measure value changes then the division of this measure over 4 must change!

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 09:00:42 GMT

give me one second, im going to show you what happend

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 09:13:29 GMT

21/03/2022 - 19/04/2022:

- 10464 YKSESE Total Qty in this range of dates... the "YKU" must be 2616 but..... is 22772,5

07/04/2022 - 19/04/2022:

- 2616 YKSESE Total Qty in this range of dates and YKU the same 22772,5

Thanks !! 😊

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 09:55:33 GMT

jmgarcia34
Ok.. what is the code you used for [YKU]. Pleasr share along with the code of [Qty] and cofirm they are both measures

Re: Divide the value of a dynamic column

jmgarcia34 — Wed, 20 Apr 2022 10:00:55 GMT

All the data is from Business Central.

How hard is it to do like in excel and select that column / 4? 😞

Re: Divide the value of a dynamic column

tamerj1 — Wed, 20 Apr 2022 10:13:16 GMT

jmgarcia34
It is simple but I've been trying to get any piece of info about your data however, until now I don't know whether the values displayed in your table visual are coming from tables or are measures that you've created! DAX is simple but in DAX we have different contexts of evaluation. This is why it makes a big difference which of them is applicable. Please help me out and give me clear answers:
1. Did you create a measure called [Qty] or this is a column in the source data you just gragged it into the values of your matix? Does it represent the sum of quantity according the current filter context?
2. Did you create a measure called [YKU] or this is a column in the source data you just gragged it into the values of your matix? What dose it represent? Why do have some blank rows?

Re: Divide the value of a dynamic column

jmgarcia34 — Thu, 21 Apr 2022 09:33:59 GMT

- The Qty measure comes directly from Business Central Web Services ( postedsalesinvoiceSalesInvLines[Quantity] ) and is the total units sold of that product in the time period indicated.

- YKU is created as a MEASURE with the formula:
"YKU = SUM(postedsalesinvoiceSalesInvLines[Quantity])/4"

YKU represents the total units sold per week (calculated by dividing the total for the month by 4 weeks).

I don't know the reason for the blank rows as it is automatic when adding the measure created with the formula

Re: Divide the value of a dynamic column

tamerj1 — Thu, 21 Apr 2022 09:36:40 GMT

Did you try

[Qty]/4

Re: Divide the value of a dynamic column

jmgarcia34 — Thu, 21 Apr 2022 10:11:32 GMT

no 😞 😞

Re: Divide the value of a dynamic column

tamerj1 — Thu, 21 Apr 2022 10:18:03 GMT

Please try

Re: Divide the value of a dynamic column

jmgarcia34 — Thu, 21 Apr 2022 10:20:35 GMT

Yes, I tried but it didn't work