Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUM by category - not date

Hi There,

 

I'm looking for a measure that will do a sum by category, not by date. My Fact table has training durations - I have Date table and other dimensions. I would like a measure that can sum durations by category.

 

My fact table looks like this:

 

 

When I do a simple sum of TrainingDuration I get a sum of all the rows - but I would like to be able to get a SUM just by category, like this:

 

 

How can that be done? I would prefer not changing the granularity of my fact table (daily).

 

Thank you for letting me join - looking forward to a long stay 🙂

 

Jasmin

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    According to your requirements, I did the following test. Since I can't directly group the Category field and find the value of sum, I first create a column to categorize the Category field. Then create a virtual list, and calculate the total value of TraingDuration according to the column classification. The reference results are as follows:

    Cat_result = IF('Table'[Category]="Science",1,2)
    sum_duration =
CALCULATE (
    SUM ( 'Table 2'[TrainingDuration] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Cat_result] = EARLIER ( 'Table 2'[Cat_result] )
    )
)
    Table 2 =
SUMMARIZECOLUMNS (
    'Table'[Category],
    'Table'[TrainingDuration],
    'Table'[Teacher],
    'Table'[Room],
    'Table'[Cat_result]
)
    Table 3 =
SUMMARIZECOLUMNS (
    'Table 2'[Category],
    'Table 2'[Teacher],
    'Table 2'[Room],
    'Table 2'[sum_duration]
)

    Here is the sample pbix file.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

     

    Please try the below measure.

     

    Training Durations =
    VAR newtable =
    SUMMARIZE (
    Data,
    Data[Category],
    Data[Name],
    "@duration", MAX ( Data[TrainingDuration] )
    )
    VAR groupbycategory =
    GROUPBY (
    newtable,
    Data[Category],
    "@totalduration", SUMX ( CURRENTGROUP (), [@duration] )
    )
    RETURN
    SUMX ( groupbycategory, [@totalduration] )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jihwan - thank you!

       

      It seems to do the trick!

       

      Best!

      Jasmin

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    According to your requirements, I did the following test. Since I can't directly group the Category field and find the value of sum, I first create a column to categorize the Category field. Then create a virtual list, and calculate the total value of TraingDuration according to the column classification. The reference results are as follows:

    Cat_result = IF('Table'[Category]="Science",1,2)
    sum_duration =
CALCULATE (
    SUM ( 'Table 2'[TrainingDuration] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Cat_result] = EARLIER ( 'Table 2'[Cat_result] )
    )
)
    Table 2 =
SUMMARIZECOLUMNS (
    'Table'[Category],
    'Table'[TrainingDuration],
    'Table'[Teacher],
    'Table'[Room],
    'Table'[Cat_result]
)
    Table 3 =
SUMMARIZECOLUMNS (
    'Table 2'[Category],
    'Table 2'[Teacher],
    'Table 2'[Room],
    'Table 2'[sum_duration]
)

    Here is the sample pbix file.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much Henry - this works and saves my day!

       

      Best,

      Jasmin