cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DJBrennan Frequent Visitor
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
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
DJBrennan Frequent Visitor
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_MPANIDCount of MonthStanding Charge/ month
190000438309512£5.00
190003542563112£5.00
190004638297012£5.00
190007014321912£5.00
190009086876412£5.00
19000131116609£6.00
19000161113809£6.00
19000351105939£6.00
19000351106549£6.00
19000421109329£6.00
19000431107759£6.00
19000490939109£6.00
19000913799887£6.00
19000214263053£6.00
Moderator v-qiuyu-msft
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]))

 

q2.PNG

 

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.
DJBrennan Frequent Visitor
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_MPANIDCountOfMonthStanding Charge/ monthMultiply
190000438309512£5.00300
190000438309512£6.00648
19000131116609£5.00225
19000131116609£6.00486
19000161113809£5.00225
19000161113809£6.00486
19000214263053£5.0075

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors