Forum Discussion
Multiply Count by Value in Related Table Column
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
Thank you for your help. However, I'm getting these results rather than the correct ones you show in your table. Each MPANID is duplicated and the duplicate has the wrong standing charge and the Multiply value is extended by an apparently random factor. Any thoughts?
Elec_MPANID CountOfMonth Standing Charge/ month Multiply 1900004383095 12 £5.00 300 1900004383095 12 £6.00 648 1900013111660 9 £5.00 225 1900013111660 9 £6.00 486 1900016111380 9 £5.00 225 1900016111380 9 £6.00 486 1900021426305 3 £5.00 75