Forum Discussion

jamuka's avatar
jamuka
Helper IV
6 years ago
Solved

Divide between two tables

Hello, I want to divide my total sales amount in Sales Table to total visitor number based on location in Visitors Table. Tables have inactive relationship, based on store location (there are two l...
Need Help
Show and Tell
  • AlB's avatar
    AlB
    6 years ago

    Hi jamuka 

    Since the relationship is inactive, place the fields shown from the Sales table in a table visual and create a measure:

    NumVisitors =
CALCULATE (
    SUM ( VistorsT[Visitor] ),
    FILTER (
        ALL ( VistorsT ),
        VisitorsT[Store Location] = SELECTEDVALUE ( SalesT[Store Location] )
            && VisitorsT[Date] = SELECTEDVALUE ( SalesT[Date] )
    )
)

     This will give you the number of visitors in that location and day. Yo can use that to calculate the quotient as required

    Please mark the question solved when done and consider giving kudos if posts are helpful.

     Cheers 

AlB's avatar
AlB
Community Champion
6 years ago

Hi jamuka 

Since the relationship is inactive, place the fields shown from the Sales table in a table visual and create a measure:

NumVisitors =
CALCULATE (
    SUM ( VistorsT[Visitor] ),
    FILTER (
        ALL ( VistorsT ),
        VisitorsT[Store Location] = SELECTEDVALUE ( SalesT[Store Location] )
            && VisitorsT[Date] = SELECTEDVALUE ( SalesT[Date] )
    )
)

 This will give you the number of visitors in that location and day. Yo can use that to calculate the quotient as required

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

jamuka's avatar
jamuka
Helper IV
6 years ago

Hello AlB ,

 

thanks for your help. Measure is working but I realized my question has missing information, I updated it.

I want to show values based on location and product category level and filter it with a slicer based on year month (e.g Jan '20).

So I tweaked my Visitor Table and add year month column, then tweaked your formula too.

Although it shows correct values on product category and shows subtotal on location level it didn't show on total values on Total level. I guess I have to change my Sales Table, add visitor amount to it then tried to find another way to calculate with correct numbers.