Need help with a measure in power query - excel

[AmeenVanakar]

Hi,

I need help with a formula to show total capacity per day by individual service from the table below.

Basically I need the results to show exact total capacity number as shown in the data table below. Please note that the the below table itself is the data source.

Service Category	Hours Per Day	Capacity	Total Capacity Per Day
Service 1	16	16	256
Service 2	16	3	48
Service 3	16	1	16
Service 4	16	2	32
Service 5	16	1	16
Service 6	16	3	48
Service 7	16	3	48
Service 8	16	2	32
Service 9	16	3	48
Service 10	16	8	128
Service 11	16	2	32
Service 12	16	2	32

[AmeenVanakar]

Thanks! This worked.

[SundarRaj · Accepted solution]

Hi @AmeenVanakar ,
Try this. I have attached the file below. Thanks

Regards,

[Nasif_Azam]

Hey  @AmeenVanakar ,

You can calculate Total Capacity Per Day inside Power Query without using DAX. Since the total is simply the product of Hours Per Day and Capacity, follow these steps:

1. Load your data into Power Query.

2. Go to the Add Column tab and choose Custom Column.

3. In the formula field, type:

= [Hours Per Day] * [Capacity]

4. Give the column the name Total Capacity Per Day and confirm.

This will return the exact figures from your example for instance, if Hours Per Day = 16 and Capacity = 16, the total will be 256. If you prefer to do it directly in Excel after loading the data, you can add a column in the table and use:

=[@Hours Per Day] * [@Capacity]

Both methods will give the same outcome; using the Power Query approach ensures the calculation is part of your data transformation process.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[AmeenVanakar]

Thanks for the help!