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

[Anonymous]

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.

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 

[v-xiaotang]

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]

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

[parry2k · Accepted solution]

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

[Anonymous]

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.