Divide the value of a dynamic column

[jmgarcia34]

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!

[jmgarcia34]

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.

[tamerj1]

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

[tamerj1]

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?

[jmgarcia34]

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

[tamerj1]

@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?

[jmgarcia34]

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

[tamerj1]

@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?

[jmgarcia34]

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.

[tamerj1]

@jmgarcia34 

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

[jmgarcia34]

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 !! 😊

[tamerj1]

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

[jmgarcia34]

All the data is from Business Central.

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

[tamerj1]

@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?

[jmgarcia34]

- 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

[tamerj1]

Did you try 

[Qty]/4

[jmgarcia34]

no 😞 😞

[tamerj1]

Please try

[jmgarcia34]

Yes, I tried but it didn't work

[tamerj1]

@jmgarcia34 

What did you get?

[jmgarcia34 · Accepted solution]

I HAVE IT!!!!!! (media producto)

process:

I have gone to "DATA" and selected the table where the information is taken from where the column QUANTITY was.

I have created the column "AVERAGE PRODUCT" and use the formula (without SUM):

"Average Product = postedsalesinvoicesSalesInvLines[Quantity]/4".

Being a column in the data table and not directly in the report I have obtained:

Then I went to the report and added the measure

and YEEEEEP and change with the total quantity and data range

THANKKKKKKKKKKKKKKKKKKKKKKKKSSSS

Thanks to our call I came up with the idea of adding the column directly in DATA and not in the REPORT as we were trying to do.