Forum Discussion
Divide 2 different columns from different tables and linked with 2 relations
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.
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 |
Super UserHi,
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.