How to multiply a measure from table 1 to table 2 at the same aggregation.

[fast_rabbit852]

I have two tables as below:

1) Table A 

I want to build a measure which is a ratio of Day Of Week Occurance by Location/Total occurance of Day of Week. This measure should be able to change based on the date range selected in the slicer. Example: For location A there are 2 rows for Sunday out of total of five Sundays. 2/5 or 40%  is what I want the measure to show, if the date range is narrowed to 07/20-07/22 I want for location A and Sunday to be 1/3 or 33.33%.

2) Table B

Now I want to multiply the result of measure from table A to Table B by doing an Average of Column "Yes" by Day of Week & Location. So For location A & Sunday it should be 72*0.4=28.

[v-kpoloju-msft · Accepted solution]

Hi @fast_rabbit852,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @MohamedFowzan1, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @fast_rabbit852,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @fast_rabbit852,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @fast_rabbit852,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[MohamedFowzan1]

Hi @fast_rabbit852 

Build ratio measure for table A

A - DOW Ratio = 

DIVIDE(

    COUNTROWS(TableA),

    CALCULATE(

        COUNTROWS(TableA),

        REMOVEFILTERS(TableA[Location])

    )

Compute Average "Yes" in Table B

B - Average Yes = AVERAGE(TableB[Yes])

Combined Measure (Multiply the Two)

Result = 

VAR RatioA = 

    CALCULATE(

        DIVIDE(

            COUNTROWS(TableA),

            CALCULATE(

                COUNTROWS(TableA),

                REMOVEFILTERS(TableA[Location])

            )

        ),

        -- Optional: add a filter if a relationship is missing:

        FILTER(TableA, TableA[Location] = SELECTEDVALUE(TableB[Location]) && TableA[DayOfWeek] = SELECTEDVALUE(TableB[DayOfWeek]))

    )

VAR AvgYesB = 

    CALCULATE(

        AVERAGE(TableB[Yes])

    )

RETURN

    RatioA * AvgYesB