Forum Discussion

Jorgast's avatar
Jorgast
Resolver II
5 years ago
Solved

Monthly Sum as a Measure

Hello BI Friends,

 

I need your help. I am trying to get the monthly sum as a measure for each month. The Category table is connected to a date table.

I have tried to calculate using Allselected, allexcept, and filter. None of these seem to work. I am not sure what I am missing.

 

Thanks in Advance.

 

MMMCategoryCount
May-21 15070
May-21Contract31029
May-21Commercial117946
May-21Industrial60420
May-21Pharma131
May-21Residential

417034

 

MMMCategoryCountDesired Results
May-21 15070641630
May-21Contract31029641630
May-21Commercial117946641630
May-21Industrial60420641630
May-21Pharma131641630
May-21Residential417034641630

 

MMMCategoryCount
Jun-21 16496
Jun-21Contract36503
Jun-21Commercial136074
Jun-21Industrial68410
Jun-21Pharma163
Jun-21Residential497232

 

MMMCategoryCountDesired Results
Jun-21 16496754878
Jun-21Contract36503754878
Jun-21Commercial136074754878
Jun-21Industrial68410754878
Jun-21Pharma163754878
Jun-21Residential497232754878

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Jorgast,

     

    Try this measure:

     

    Monthly Sum = 
CALCULATE (
    SUM ( Category[Count] ),
    ALL ( Category ),
    VALUES ( DimDate[Month Year] )
)

     

    The Category table has a relationship with DimDate:

     

     

    The visual uses DimDate[Month Year]:

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Jorgast,

     

    Try this measure:

     

    Monthly Sum = 
CALCULATE (
    SUM ( Category[Count] ),
    ALL ( Category ),
    VALUES ( DimDate[Month Year] )
)

     

    The Category table has a relationship with DimDate:

     

     

    The visual uses DimDate[Month Year]: