cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX Fetch base value of 1 column and multiply it to all the rows of another column

Need help in DAX. I have one table that contains data related to multipliers for each year, like the follows:

***********************************

Table Multiplier

********************************

Another table is there which has multiple 'values' for the year column like the follows:

******************************************

Table Entities

*************************************************

There's a 1- * relation between the tables based on the year column.

Question: What I have to achieve is access the base/starting"Value" of every entity (base year being 2011-12) and multiply only that value by all the rows in the 'Multiplier' column.

The solution for entity A would look something like below:

Base value remains 20 for every year, then that value gets multiplied by the multiplier which changes every year.

Now, to get the Base value, I used the following to create a measure:

Base value = CALCULATE(SUM('Entities'[Value]), Multiplier[Year] = "2011-12")

For the base*multiplier column: Base * multiplier = [Base value] * SUM('Multiplier'[Multiplier])
The result of the above is:

So, it is generating correct base value but then not moving forward to multiply each year's multiplier with the base value. I Tried other filter contexts too using KEEPFILTER, ALL within the CALCULATE, but nothing seems to generate the value I want.

Some people I know who are good and can help are tagged, but others are most welcome to help too.
@v-chenyue-msft @parry2k @V-pazhen-msft @amitchandak @lbendlin @Ashish_Mathur
1 ACCEPTED SOLUTION
Super User

@Anonymous the easiest would be to create a calculated table using the following DAX expression:

``````Table =
CROSSJOIN (
SELECTCOLUMNS (
Entities,
"Entity", Entities[Entity],
"Value", Entities[Value]
),
Multiplier
),
"Value with Multiplier", [Value] * [Multiplier]
)``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

4 REPLIES 4
Community Support

Hi @Anonymous

Thanks for reaching out to us.

>> So, it is generating correct base value but then not moving forward to multiply each year's multiplier with the base value. I Tried other filter contexts too using KEEPFILTER, ALL within the CALCULATE, but nothing seems to generate the value I want.

Can you fill this table with the expected values? thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-xiaotang Thanks for such a prompt reply. The required table would look like this:

Where in, obviously, the values in the last column would be the result of the multiplication shown.

Regards,

Roopansh

Super User

@Anonymous the easiest would be to create a calculated table using the following DAX expression:

``````Table =
CROSSJOIN (
SELECTCOLUMNS (
Entities,
"Entity", Entities[Entity],
"Value", Entities[Value]
),
Multiplier
),
"Value with Multiplier", [Value] * [Multiplier]
)``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

``````New Index table =
CROSSJOIN (
SELECTCOLUMNS (
Entities,
"Entity", 'Entities'[Entity],
" Base year value", CALCULATE(SUM('Entities'[Value]), 'Entities'[Year] = "2011-12" )
),
Multiplier
),
"Value with Multiplier", [Base year value] * Multiplier[Multiplier]
)``````

Thanks a bunch man. I'd love to hear from you what should be the best source to learn dax from. I'm currently reading a book by Matt Allington called Supercharge Power bi.