Division between 2 Different source tables

[SpiroswayGR]

Dear all,

First of all ,  we have 2 tables : On 1st table , we have revenue data (fake data) and on 2nd table we have (Days)_ and we want to divide revenue with days and create a new table.

Pos foods = Category Description

Codes = BB , FB , HB

And Numbers right is revenue 

Also we have filters on specific Shop with his code = 1 

2nd table (Code per shop) with days

So when we filter shop 1 and get data from table , somehow we want to divided on new table with the numbers of table 2 in column 1 cause column 1 is shop1. If we choose shop2 we want to divide with 2nd column from table2 etc.

Example : BB : 674,51 / 68

FB : 79,22 / 6 

HB : 666,18 / 26

Both tables are connnected with Calendar Table on Date.

Thank you for your help.

Kind Regards,

Spyros 

[SpiroswayGR]

@v-yiruan-msft 

Thank you for your time.

I will test it to see how it works.

[v-yiruan-msft · Accepted solution]

Hi @SpiroswayGR ，

You can create a measure as below, please find the details in the attachment.

Measure = 
VAR _rev =
    CALCULATE (
        SUM ( 'Table1'[Revenue] ),
        FILTER ( 'Table1', 'Table1'[POS FOODS] = SELECTEDVALUE ( 'Table2'[Code] ) )
    )
RETURN
    DIVIDE ( _rev, CALCULATE ( SUM ( 'Table2'[Days] ) ), 0 )

If the above one is not your expected result, please provide some sample data with Text format and your expected result with backend logic and specific examples. Thank you.

Best Regards

[SpiroswayGR]

@v-yiruan-msft  Thank you again very much.

It was working perfecty with some minor fixes cause i am using "Measures" to calculate revenue so i had to remove "SUM" in both cases and worked perfectly.

Also i was trying to calculate also for LY on same period , i have the revenue_LY , i have days in LY but somehow divide is not working.