Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to sum up at a lower granularity?

I created a DAX table. The granularity of the table from low to high is Expiry_Date_Year_Month, Expiry_Member_Type, Renewal_Channel. Below is the DAX code.

 

I have trouble calculating the "Number_of_Record" measure. It is the number summed up at the Expiry_Member_Type level. The code I have below doesn't do that but shows the number of records at the Renewal_Channel level. 

 

Any ideas on aggregating the number of records at the Expiry_Member_Type please? Thank you.

 

 

MemberRenewChannel =
UNION(
summarize(
SELECTCOLUMNS(MemberRenew,
, "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
, "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
, "Renewal_Channel", MemberRenew[Renewal_Channel]
),
[Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
, "Measure", "Renewal"
, "Value", SUM(MemberRenew[Renewal])
), -- Renewal
summarize(
SELECTCOLUMNS(MemberRenew,
, "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
, "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
, "Renewal_Channel", MemberRenew[Renewal_Channel]
),
[Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
, "Measure", "Number_of_Record"
, "Value", calculate(sum(MemberRenew[Renewal]), all(MemberRenew[Expiry_Date_Year_Month], MemberRenew[Expiry_Member_Type] ) )

) -- Number_of_Record
) -- union

 

  • Hi Anonymous,

     

    Please try this:

    MemberRenewChannel =
    UNION(
    summarize(
    SELECTCOLUMNS(MemberRenew,
    , "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
    , "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
    , "Renewal_Channel", MemberRenew[Renewal_Channel]
    ),
    [Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
    , "Measure", "Renewal"
    , "Value", SUM(MemberRenew[Renewal])
    ), 
    summarize(
    SELECTCOLUMNS(MemberRenew,
    , "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
    , "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
    , "Renewal_Channel", MemberRenew[Renewal_Channel]
    ),
    [Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
    , "Measure", "Number_of_Record"
    , "Value", calculate(sum(MemberRenew[Renewal]), all(MemberRenew[Renewal_Channel] ) )
    
    ) 
    ) 

     

    Regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous,

     

    Please try this:

    MemberRenewChannel =
    UNION(
    summarize(
    SELECTCOLUMNS(MemberRenew,
    , "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
    , "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
    , "Renewal_Channel", MemberRenew[Renewal_Channel]
    ),
    [Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
    , "Measure", "Renewal"
    , "Value", SUM(MemberRenew[Renewal])
    ), 
    summarize(
    SELECTCOLUMNS(MemberRenew,
    , "Expiry_Date_Year_Month", MemberRenew[Expiry_Date_Year_Month]
    , "Expiry_Member_Type", MemberRenew[Expiry_Member_Type]
    , "Renewal_Channel", MemberRenew[Renewal_Channel]
    ),
    [Expiry_Date_Year_Month], [Expiry_Member_Type], [Renewal_Channel]
    , "Measure", "Number_of_Record"
    , "Value", calculate(sum(MemberRenew[Renewal]), all(MemberRenew[Renewal_Channel] ) )
    
    ) 
    ) 

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you, Yuliana! It works.