Forum Discussion

baronraghu's avatar
baronraghu
Helper III
8 years ago
Solved

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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:

     

  • GabrielSantos's avatar
    GabrielSantos
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      baronraghu
      Helper III
      8 years ago

      Thanks Anonymous

       

      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)

       

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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: