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.
Hi All,
I am stuck in a simple problem hope you could help
I have two tables
Table 1 has data on the details of the retail outlet
RO name Fuel sold
A Gasoline
A Diesel
A Kero
B Gasoline
C Diesel
C Gasoline
Table 2 has data on daily sales by RO
RO Date Daily Sale
A 1/8/017 $ 100
A 2/8/017 $ 200
B 1/8/2017 $50
C 1/8/2017 $10
B 2/8/2017 $ 40
now the result that i want is as below
Gasoline $400 (sold by A,B,C)
Diesel $310 (sold by A,C)
Kero $300 (sold by A)
Could you please help me on this
Thanks
Solved! Go to Solution.
Make sure the relationship is setup as "both" not "single" under Cross Direction Fitler. I was able to get it to work with a model like this:
Actually, I'm not sure the figures you have are meaningful. Your sales table has an outlet indicator (A,B,C), but no fuel type indicator. Because each outlet sells multiple types of fuel, it's impossible to attribute any sale to a specific fuel type.
The reason you see $4,265 for Diesel and Gasoline and $3,266 for Kerosene are because Diesel and Gasoline are sold at A,B, and C where Kerosene is only sold at A and C.
What happens is the FuelType table is filtered by fuel type, and that filter carries forward through the bi-directional filter to the Sales table.
The Sales table can only be filtered by the outlet type (A,B,C). Therefore, for Kerosene, the outlets of A and C are carried forward to the Sales table and the amount is summed and returned.
Hi,
Sorry but you can't get the result you want because total sold fuel type (amount-wise) makes $ 1010 but in table 2 i see $ 400 in total. I didn't get the logic (you should create a relationship between tables to have kind of a table you want but first you need to fulfill missing informations)
Thanks @Anonymous
I am updating the problem hope its help
I have two tables
Table 1 Outlet and type of fuel sold by them
A | Gasoline |
A | Diesel |
A | Kerosene |
B | Diesel |
B | Gasoline |
C | Kerosene |
C | Diesel |
C | Gasoline |
Table 2 has date wise sale value by each outlet
Date | Outlet | Sale |
01-08-17 | A | 318 |
01-08-17 | A | 364 |
01-08-17 | A | 230 |
01-08-17 | A | 172 |
01-08-17 | A | 173 |
01-08-17 | A | 194 |
01-08-17 | A | 374 |
02-08-17 | B | 468 |
02-08-17 | B | 131 |
02-08-17 | B | 400 |
03-08-17 | C | 245 |
03-08-17 | C | 239 |
03-08-17 | C | 358 |
03-08-17 | C | 191 |
03-08-17 | C | 264 |
03-08-17 | C | 144 |
The total sale by each outlet is easy to calculate using the sum function on table 2).
But what want is to know how much each fuel was sold. For example if the sale for Gasoline was made at Outlet A&B&C which will be the their total sale is 4265
Similarly if I want to know the sale of Kerosene then it will be the sale made by A&C. (1825+1441= 3266)
I have made a unique table on Outlets and tried to build relationship between the two. BUt some how when I am trying to get the sale made by each fuel the end result what i am getting is the same number for all three category like shown below
Gasoline | 4265 |
Diesel | 4265 |
Kerosene | 4265 (insted of 3266) |
Hi,
It looks like there is a probem in the 2 Tables. From Table2, one can read that the total sale from outlet B was 468 on 02-08-2017. Now how does one know where this was all Diesel or all Gasonline or a combination of the two.
Make sure the relationship is setup as "both" not "single" under Cross Direction Fitler. I was able to get it to work with a model like this:
Hi @Anonymous
Thanks for your reply
I did the same but for kerosene i am getting 4265 insted of 3266. The product is only sold at A&C outlet
The result i am getting is
Kerosene should be 3266
Hi @Anonymous
I tried again it worked. Actually we need to do the direction setting to "Both" for both the relation. Thanks for the suggestion.
Could you please tell me the rationale for this
Actually, I'm not sure the figures you have are meaningful. Your sales table has an outlet indicator (A,B,C), but no fuel type indicator. Because each outlet sells multiple types of fuel, it's impossible to attribute any sale to a specific fuel type.
The reason you see $4,265 for Diesel and Gasoline and $3,266 for Kerosene are because Diesel and Gasoline are sold at A,B, and C where Kerosene is only sold at A and C.
What happens is the FuelType table is filtered by fuel type, and that filter carries forward through the bi-directional filter to the Sales table.
The Sales table can only be filtered by the outlet type (A,B,C). Therefore, for Kerosene, the outlets of A and C are carried forward to the Sales table and the amount is summed and returned.
I know it doesnt make sense as you have correctly pointed out. What I actually trying to do is
1) There are many other fuels that are sold at the outlets. Some generate high revenue some generate low revenue
2) I am going to use a word cloud visual just to highlight which all products are solds. The size of each visual will depend upon revenue generated. As in this case Gasoline and Diesel will be shown bigger compared to kerosene
Hope it helps
It looks like your relationship between Table 1 and Table 3 is a one to many instead of a bidirectional relationship. Try changing that and evaluate.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |