cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Salma_00 Frequent Visitor
Frequent Visitor

how to multiply different columns with a dax measure

Hi,

So basically, what i want to do is multiply 3 columns within themselves the thing is for a row in column 1 it has to be multiplied with the set before the row in question for column 2 and the set of rows after the column in question for column 3, I don’t know if I explained my problem properly but basically this is what I want to do

Capture1.PNG       

for the first row

    

Capture2.PNG

 

the middle

 

Capture3.PNG

The last row  

 

 

For my dax measure I tried with the following command

commande&é.PNG

 

But it gave me this error

erreur12.PNG

 

 

For the ones that do not speak French it states that the context where I put earlier does not exist

My index column is a dax measure that I wrote to make calculation easy for me

 

6 REPLIES 6
v-jiascu-msft Super Contributor
Super Contributor

Re: how to multiply different columns with a dax measure

Hi @Salma_00,

 

Can you share a complete sample? You can use dummy data. 

EARLIER can't have a DAX measure as parameter. You can change it into a calculated column.

how_to_multiply_different_columns_with_a_dax_measure

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Salma_00 Frequent Visitor
Frequent Visitor

Re: how to multiply different columns with a dax measure

hi, 

thank you for your reply

i tried to  do a calculated column but since my calculations involve other dax measures, my attempts weren't successful 

 

here is a sample of my data

https://drive.google.com/open?id=1VrK4umDrPXhSRwI8eYh7dnfyreuw6VAU

v-jiascu-msft Super Contributor
Super Contributor

Re: how to multiply different columns with a dax measure

Hi @Salma_00,

 

How did you do it? Your formula is fine in the Power BI. Please check it out here.

Column =
CALCULATE (
    PRODUCTX ( Tableau1, [1+R] ),
    FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] > EARLIER ( Tableau1[Index] ) )
)
    * CALCULATE (
        PRODUCTX ( 'Tableau1', [1+B] ),
        FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] < EARLIER ( Tableau1[Index] ) )
    )
    * [allocation]

how_to_multiply_different_columns_with_a_dax_measure2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Salma_00 Frequent Visitor
Frequent Visitor

Re: how to multiply different columns with a dax measure

hi,

it is because my 1+R and 1+B columns are dax measures and not regular column, i actually ahven't thought of using power bi to see if it will work thank you for your recommendation ... but as you can see it won't give you any result for the first and the last row ..so how can i write on a measure than can include them

v-jiascu-msft Super Contributor
Super Contributor

Re: how to multiply different columns with a dax measure

Hi @Salma_00,

 

Solution 1: Add the "=" operator.

Column =
CALCULATE (
    PRODUCTX ( Tableau1, [1+R] ),
    FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] >= EARLIER ( Tableau1[Index] ) )
)
    * CALCULATE (
        PRODUCTX ( 'Tableau1', [1+B] ),
        FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] <= EARLIER ( Tableau1[Index] ) )
    )
    * [allocation]

Solution 2:

Column =
VAR temp =
    CALCULATE (
        PRODUCTX ( Tableau1, [1+R] ),
        FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] > EARLIER ( Tableau1[Index] ) )
    )
        * CALCULATE (
            PRODUCTX ( 'Tableau1', [1+B] ),
            FILTER ( ALL ( 'Tableau1' ), 'Tableau1'[Index] < EARLIER ( Tableau1[Index] ) )
        )
        * [allocation]
RETURN
    IF ( ISBLANK ( temp ), 0, temp )

If they are measures, we need the original data to develop a solution or a workaround.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Salma_00 Frequent Visitor
Frequent Visitor

Re: how to multiply different columns with a dax measure

hi Dale,

Sorry to bother you again !!

but i'm kind of lost here wouldn't be the current column involved in the calculation if we ad '=' to the function

 

i'm  not sure if you understood what i'm trying to do here but just to make stuff a little bit clearer this is what i'm trying to calculate using a dax measuregrap.PNG

with at being the allocation column

(1+rt) the 1+R one and (1+bt) the 1+B one

 

these three columns are already being calculated that's why i can't do a clculated colmun cause they differ form row to row

 

 

here is a sample of my inital data with a pivot table... this is what i am currently working on (i use excel 2016 btw)

https://drive.google.com/open?id=13oA-cBC0FwEwZqyhvnZWt902Xw4-Q3OD

 

thank you so much for your help !!

 

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 367 members 3,397 guests
Please welcome our newest community members: