Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 location) between them.
When I tried to use simple divide, it divides all visitors instead of location level.
Sales table
Date | Store Location | Store No | Product Category | Product ID | Sales Amount |
1.05.2020 | A | A-1 | Cat-A | 2 | 50 |
1.05.2020 | B | B-1 | Cat-B | 3 | 75 |
1.05.2020 | A | A-2 | Cat-A | 1 | 50 |
1.05.2020 | B | B-2 | Cat-C | 4 | 75 |
2.05.2020 | A | A-1 | Cat-A | 2 | 50 |
2.05.2020 | B | B-1 | Cat-B | 5 | 75 |
2.05.2020 | A | A-2 | Cat-B | 3 | 50 |
2.05.2020 | B | B-2 | Cat-B | 7 | 75 |
Visitors table
Date | Store Location | Visitor |
1.05.2020 | A | 1.500 |
1.05.2020 | B | 2.000 |
2.05.2020 | A | 1.000 |
2.05.2020 | B | 1.750 |
in excel my expected result looks like this
Store Location | Product Category | Total Sales | Sales Per Visitor |
A | Cat-A | 150 | 0,06 |
A | Cat-B | 50 | 0,02 |
B | Cat-B | 225 | 0,06 |
B | Cat-C | 75 | 0,02 |
My Data model is
Solved! Go to Solution.
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 , you should join both tables with Store and then you can analyze data for the store , there you will be able to see both sum of sales and visitor
Hello @amitchandak,
When I join Visitor Table to Store Table and add visitor column to my visual it shows same value (total visitor) for each row instead of location's sum of visitor.
You either need a better data model or USERELATIONSHIPS.
If you made that relationship uni-directional from Sales to Visitors, it wouldn't be inactive.
Hello @Greg_Deckler thank you for your reply.
I made the relationship from sales to visitors but couldn't make it active due to ambiguity between Date and Visitors Tables.
I tried to use USERELATIONSHIPS but couldn't finda relevant sample.
also I realized my question has missing information therefore I updated it, sorry for inconvenience.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |