Divide 2 different columns from different tables and linked with 2 relations

[IvoryKoss]

Hi All,

I want to calculate the KWH per Volume

-the 2 colums are in deffernt tables

-The tables are linked by relationships trough intermediate tables

-the expected KWH per Volume shoud be per week and per factory

 

I have tried this DAX formula, but don't expected results.

KWH per Volume = 
DIVIDE(
Energy[KWH],
CALCULATE(SUM(Volume[volume]),ALLEXCEPT(Volume,Factories[factory]),ALLEXCEPT(Volume,datemaster[WEEKs])),
0
) 

Rgds,

[IvoryKoss]

One finding I did is that the result of the measure is different according the table where you created it.

[Ashish_Mathur]

Please elaborate.  What problem are you facing?

[Ashish_Mathur · Accepted solution]

Hi,

In your visual, drag week from the Datamaster table and factory from the Factories table.  Then this measure should work

=SUM(Energy[KwH])/SUM(Volume[Volume])

If this does not help, then share the link from where i can download your file.

[IvoryKoss]

Thx it works perfectly!

[Ashish_Mathur]

You are welcome.

[dhaval1001shah]

Dear Mr. Ashish,

I need solution for given below:

Thank You.

Table 1			Table 2			Requirement
Store ID	Dis Amt		Store ID	Sales		Store ID	Dis % To Sales
ABC	2		ABC	50		ABC	20%
ABC	4
BCD	2
ABC	4
BCD	6
DEF	4
DEF	2

[Ashish_Mathur]

Hi,

Create a third table with a single column which lists all unique Store ID's.  Create a relationship (Many to One and Single) from the StoreID column of the 2 tables to the StoreID column of the third table.  To your visual, drag StoreID from Table3.  Write these measures:

Dis Amount = sum('Table1'[Dis Amt])

Total sales = sum('Table2'[Sales])

Dis% to sales = divide([Dis Amount],[Total sales])

Hope this helps.

[dhaval1001shah]

Dear Mr. Ashish,

Thank yoi for your reply.

However, i am not able to get % when i select ABC from my store ID list. Over all it gets tally when i divide total discount with total sales.

However when i select store ID ABC then i must get 20% (ABC dis amt is 10 and Sales is 50 = 20%). But i am not getting the same.

Kindly help.

I followed all steps which you given above.

Thank You.

Regards

CA Dhaval Shah

[dhaval1001shah]

Dear Mr. Ashish,

Due to confidentiality i can't share my pbi file, but i can try to give all details below. Hopefully you will be able to provide solution on the same:

Table 1 - Birthday Discount - Invoice wise and Store wise (It has line items more than '000 - Particular store has many line items)

Table 2 - Sales Master - It shows store wise Sale (Only Store ID and it's Total Sale Amount)

Table 3 - Store Master -(Store ID, Store Name, City, Region etc..)

I connected - Table 1 with Table 2 on Store ID

I connected - Table 1 with Table 3 on Store ID

Now my requirements are - 

1. Total Discount per store (Which i will get from table 1)

2. Total Sales store wise (Already given in Table 2)

3. Final requirement - Store wise % to Sales

For more clarification, sample given below:

Table 1		Table 2		Requirement
Store ID	Dis Amt		Sales	Amt		Requirement (As filter box, where I can select store ID and it will reflect % to Sales
1	12		1	300		Store ID	% to Sales
2	13		2	200		1	25.33%	(76/300)	76 is Total Discount
1	16					2	20%	(40/200)	40 is Total Discount
1	10
2	8
1	18
1	20
2	19

[Ashish_Mathur]

Hi,

To your visual, drag StoreID from Table 3.  Write these measures:

Dis amount = sum('Table1'[Discount])

Total sales = sum('Table2'[Amount])

Dis (%) = divide([dis amount],[total sales])

Hope this helps.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the problem very clearly.

[v-sihou-msft]

@IvoryKoss

Based on your screenshot, both Volume and Energy are fact tables, Week and Factory are dimension tables. Since both volumn and KWH are sliced by Week and Factory, you should be able to directly have KWH divided by volume.

=DIVIDE(Energy[KWH],RELATED(Volume[volume]))

Or you can lookup corresponding volume into Energy table. 

=LOOKUPVALUE(Volume[volume],Volume[factory],Energy[factory],Volume[Week],Energy[Week])

Then use KWH column divied by above lookup column.

Regards,

[IvoryKoss]

Merci @v-sihou-msft for your answer,

however I still got errors:

=DIVIDE(Energy[KWH],RELATED(Volume[volume]))

returns : The column 'Volume[volume]' either doesn't exist or doesn't have a relationship to any table available in the current context.

and

LOOKUPVALUE(Volume[volume],Volume[factory],Energy[factory],Volume[Week],Energy[Week])

 returns:

A single value for column 'factory' in table 'Energy' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

is it possible with the 2nd option to returns a table and then sum up to have the total volume. So that the division can happen?