cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baronraghu Member
Member

DAX help

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

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
olesojg
Advisor

Re: DAX help

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:

 

9-8-2017 1-32-29 PM.png

GabrielSantos Regular Visitor
Regular Visitor

Re: DAX help

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.

9 REPLIES 9
mazedreamer Regular Visitor
Regular Visitor

Re: DAX help

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 Member
Member

Re: DAX help

Thanks @mazedreamer

 

I am updating the problem hope its help

 

I have two tables

 

Table 1 Outlet and type of fuel sold by them

 

AGasoline
ADiesel
AKerosene
BDiesel
BGasoline
CKerosene
CDiesel
CGasoline

 

Table 2 has date wise sale value by each outlet

 

DateOutletSale
01-08-17A318
01-08-17A364
01-08-17A230
01-08-17A172
01-08-17A173
01-08-17A194
01-08-17A374
02-08-17B468
02-08-17B131
02-08-17B400
03-08-17C245
03-08-17C239
03-08-17C358
03-08-17C191
03-08-17C264
03-08-17C144

 

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

 

Gasoline4265
Diesel4265
Kerosene4265 (insted of 3266)

 

 
olesojg
Advisor

Re: DAX help

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:

 

9-8-2017 1-32-29 PM.png

baronraghu Member
Member

Re: DAX help

Hi @olesojg

 

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

 

fuel.PNG

 

The result i am getting is

fiel-1.PNGKerosene should be 3266

GabrielSantos Regular Visitor
Regular Visitor

Re: DAX help

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.

baronraghu Member
Member

Re: DAX help

Hi @olesojg

 

I tried again it worked. Actually we need to do the direction setting to "Both" for both the relation. Thanks for the suggestion.

 

fuel-3.PNG

 

Could you please tell me the rationale for this

GabrielSantos Regular Visitor
Regular Visitor

Re: DAX help

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 Member
Member

Re: DAX help

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

Highlighted
Super User
Super User

Re: DAX help

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.