Forum Discussion
Multiply Count by Value in Related Table Column
Hi, I have a table that contains a supply number and month of invoice. So one supply number may have 12 entries (12 monthly invoices) and another perhaps 3. I have another table that contains a standing charge per month for each supply number. The tables are properly related and I can produce a table containing Supply Number, Count distinct month and the Standing Charge from the related table. For each supply number, I want to multiply the number of months by the Standing Charge to determine total monthly charge in the year (which will be sliced). So if the Standing Charge for Supply No. 1 is 5 and the number of months it was invoiced is 3 the answer is 15. I believe the solution is to do with DistinctCount but can't work out the logic.
Many thanks in advance,
Denis
4 Replies
Can you post some sample data so that we can recreate the model and determine the correct calculation required?
This is the table I built in Power BI exported to Excel. The first two columns come from the Supplies table and the price from the related table Is this sufficient?
Elec_MPANID Count of Month Standing Charge/ month 1900004383095 12 £5.00 1900035425631 12 £5.00 1900046382970 12 £5.00 1900070143219 12 £5.00 1900090868764 12 £5.00 1900013111660 9 £6.00 1900016111380 9 £6.00 1900035110593 9 £6.00 1900035110654 9 £6.00 1900042110932 9 £6.00 1900043110775 9 £6.00 1900049093910 9 £6.00 1900091379988 7 £6.00 1900021426305 3 £6.00 Hi DJBrennan,
You can create a table use DAX below:
Table = SUMMARIZE('Table1','Table1'[Elec_MPANID],"CountOfMonth",DISTINCTCOUNT(Table1[Month]))
Then create a measure below to calculate multiply:
Multiply = CALCULATE(SUM('Table'[CountOfMonth]))*CALCULATE(SUM('Table2'[Standing Charge/ month]))
Best Regards,
Qiuyun Yu
