Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jmgarcia34
Helper I
Helper I

Divide the value of a dynamic column

Hi, 

 

My name is Jose and im new with power BI:

 

I have this:

 

jmgarcia34_0-1650376370691.png

 

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

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

 

jmgarcia34_0-1650546343425.png

process:

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

jmgarcia34_2-1650546581591.png

 

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:

jmgarcia34_1-1650546556321.png

Then I went to the report and added the measure

jmgarcia34_3-1650546630297.png

and YEEEEEP and change with the total quantity and data range

jmgarcia34_4-1650546680640.pngjmgarcia34_5-1650546696375.png

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.

 

 

 

View solution in original post

24 REPLIES 24
jmgarcia34
Helper I
Helper I

Hi and thanks in advance 😄

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

jmgarcia34_0-1650379078227.png

The idea is easy in excel:

A column that gives as result:

Qty/4

*Qty is variable depending on the selected date.

 

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

tamerj1
Super User
Super User

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?

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

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

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

@jmgarcia34 
What is the X-Axis of this chart? Is it Date?
1.png
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?

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.

@jmgarcia34 

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

 

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

 

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

 

jmgarcia34_0-1650445497239.png

 

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

 

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

jmgarcia34_2-1650445938924.png

Thanks !! 😊

 

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

All the data is from Business Central.

 

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

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

- 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

 

 

Did you try 

[Qty]/4

no 😞 😞

Please try

Yes, I tried but it didn't work

@jmgarcia34 

What did you get?

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

 

jmgarcia34_0-1650546343425.png

process:

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

jmgarcia34_2-1650546581591.png

 

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:

jmgarcia34_1-1650546556321.png

Then I went to the report and added the measure

jmgarcia34_3-1650546630297.png

and YEEEEEP and change with the total quantity and data range

jmgarcia34_4-1650546680640.pngjmgarcia34_5-1650546696375.png

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.

 

 

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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