cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 4
Super User

## Re: Multiply Count by Value in Related Table Column

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

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: 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
Moderator

## Re: Multiply Count by Value in Related Table Column

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Multiply Count by Value in Related Table Column

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

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors