Multiply Count by Value in Related Table Column

[DJBrennan]

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

[Greg_Deckler]

Can you post some sample data so that we can recreate the model and determine the correct calculation required?

[DJBrennan]

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

[v-qiuyu-msft]

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

[DJBrennan]

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