Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |