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.
Super UserHi,
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.
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
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file and show the problem very clearly.
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.