DAX help

[baronraghu]

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

[Anonymous · Accepted 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:

[Anonymous]

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)

[baronraghu]

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)

[Ashish_Mathur]

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.

[Anonymous · Accepted 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:

[baronraghu]

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

[baronraghu]

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

[GabrielSantos · Accepted solution]

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.

[baronraghu]

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

[GabrielSantos]

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.