Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

Roopansh_0-1662456615586.png

 

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

 

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

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

Table Entities

Roopansh_1-1662456662419.png

 

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

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:

Roopansh_3-1662456838276.png

 

 

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:
Roopansh_2-1662456796647.png

 


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.
 
Please help. Thanks in advance.
 
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
parry2k
Super User
Super User

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

 

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

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

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.

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
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

vxiaotang_0-1662535621752.png

 

 

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:

Roopansh_0-1662609447068.png

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

 

Regards,

Roopansh

 

parry2k
Super User
Super User

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

 

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

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

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

Hi @parry2k , Thanks for your reply. Here's your code with v small changes that worked.

 

New Index table = 
ADDCOLUMNS (
    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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors