Unable to calculate a table column by a measure

[MyWeeLola]

Good morning

I am having a brain melt as I suspect im  missing something fundemental.

I have 2 tables

TblActivityPivot

[EngagementDate]

[EngagementID]

[Activity]

TblActivityScore

[Activity]

[Score]

I have a pivoted table where I do a distinct count of [EngagementID] as a measure

I have added that measure to a table with the [Activitiy] from that table.

This now gives me a table where I see the count of the [Activity], which is correctly changed by filter selection

I have a second table where I have the [Activity] in one column and a whole number [score] which is associated with that value.

There is a one to many relationship between the Activity columns in both tables

I now want to multiply the count of activities with the score to get a new value and display this in different tables. Ideally as a measure.

How do I do this?

I look forward to any assistance

Allan

[v-nmadadi-msft]

Hi @MyWeeLola,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @MyWeeLola,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @MyWeeLola,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi  @MyWeeLola,
Thanks for reaching out to the Microsoft fabric community forum.

Since you have already established a relationship between the two tables, the next step is to create a measure that multiplies the count of activities by their corresponding scores to generate a new value.

Here’s the DAX query you can use:

ActivityWeightedScore =

SUMX(

    TblActivityPivot,

    [EngagementCount] * RELATED(TblActivityScore[Score])

)

The output I have received for that is

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[freginier]

Hi allan!

To calculate the value you're looking for, where the count of activities is multiplied by the score from the second table, follow these steps:

1. Create a relationship between the [Activity] columns in both tables if one doesn't already exist. Since you've mentioned a one-to-many relationship, Power BI will automatically set this up once the two tables are linked via the [Activity] column.

2. Create the Measure:

   • You need to create a new measure that multiplies the count of [EngagementID] by the [Score] from the second table.

   • Use this DAX formula to create the measure:

   • ActivityScore =
     SUMX(
     TblActivityPivot,
     CALCULATE(
     COUNTROWS(TblActivityPivot),
     TblActivityPivot[Activity] = TblActivityScore[Activity]
     ) * RELATED(TblActivityScore[Score])
     )

1. Use the Measure:

   • You can now use this new measure in your visualizations across different tables in Power BI.

This approach should allow you to calculate the new value and display it in your reports or tables.

Hope this helps!

😁😁