cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Divide the value of a dynamic column

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!
1 ACCEPTED SOLUTION
Helper I

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.

24 REPLIES 24
Helper I

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.

Super User

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

Super User

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?

Helper I

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

Super User

you replied to the old measage

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

Helper I

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

Super User

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

Helper I

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.

Super User

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

Helper I

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

Super User

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

Helper I

All the data is from Business Central.

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

Super User

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

Helper I

- 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

Super User

Did you try

[Qty]/4

Helper I

no 😞 😞

Super User

Helper I

Yes, I tried but it didn't work

Super User

What did you get?

Helper I

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors