How to calculate a sum based on multiple rows of a different table

[Anonymous]

Please, can someone help me to make sum of time periods of each state (can occure more than once)? The summed data in new table will be used for further calculations.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use pivot to do it.

1. Import the file into Power BI. 

2. Open the query editor.

3. Remove the "change_date" and "from_state" column. Now you get a three-column table.(the following image is my test file) 

4. Then select "state" column, click the "Pivot Column" in the transform tab.

5. Click Close & Apply, you will get the result.

[Anonymous]

Thank you very much for your help 🙂

[Anonymous]

Hi @Anonymous 

Try the "group by" function in power query editor. 

there you can sum based on differnt columns. 

if you provide an example report i can show you if unclear.

kr 

[Anonymous]

The Group function would be perfect only if I could specify the particular value for filtered rows (issue key, current state=idea, etc.) for each new column the Group function would create: