Forum Discussion

jeblais's avatar
jeblais
New Member
6 months ago
Solved

Process data table to obtain subtotal table

Hello, I have a Database-like table which resumes Work Orders (WO) and Tasks for those WO where there's as many lines as there's Tasks. For example, I have 5 tasks for the same WO where each has their own planned work hours, actual work hours, planned costs, actual costs, etc : 

I would like to have a second table, from that table, to show WOs with the subtotals only, and keep WO-related data only :

Is it something that can be done?

 

Thanks!

 

PS. I know Matrix can do that, but I don't want show the data necessarily with a table.

 

Sub-Totals
tables
  • Jihwan_Kim's avatar
    Jihwan_Kim
    6 months ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    When creating the second table, I tried to write DAX like below.

    Please check the below picture and the attached pbix file.

     

    First table.

     

     

    Second table

     

    SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Learn

     

    Expected result table = 
SUMMARIZECOLUMNS (
    data[plant section],
    data[order type],
    data[mainactivitytype],
    data[order],
    data[description],
    "Work Sum", SUM ( data[work] ),
    "Actual work sum", SUM ( data[actual work] )
)

     

  • cengizhanarslan's avatar
    cengizhanarslan
    6 months ago
    1. Transform data (Power Query)
    2. Select your base table
    3. Home → Group By
    4. Group by the WO keys you want to keep, e.g.:
      • Work Order
      • Plant section
      • any other WO-level attributes that are the same across tasks
    5. Add aggregations:
      • Planned Hours = Sum of planned hours column
      • Actual Hours = Sum
      • Planned Cost = Sum
      • Actual Cost = Sum

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    6 months ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    When creating the second table, I tried to write DAX like below.

    Please check the below picture and the attached pbix file.

     

    First table.

     

     

    Second table

     

    SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Learn

     

    Expected result table = 
SUMMARIZECOLUMNS (
    data[plant section],
    data[order type],
    data[mainactivitytype],
    data[order],
    data[description],
    "Work Sum", SUM ( data[work] ),
    "Actual work sum", SUM ( data[actual work] )
)

     

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 months ago
    1. Transform data (Power Query)
    2. Select your base table
    3. Home → Group By
    4. Group by the WO keys you want to keep, e.g.:
      • Work Order
      • Plant section
      • any other WO-level attributes that are the same across tasks
    5. Add aggregations:
      • Planned Hours = Sum of planned hours column
      • Actual Hours = Sum
      • Planned Cost = Sum
      • Actual Cost = Sum
  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    Hi jeblais,

    Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Jihwan_Kim & cengizhanarslan for sharing valuable insights.

     

    Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

     

    Thank you for being part of the Microsoft Fabric Community.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    Hello jeblais,

    Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

    Thank you.