Forum Discussion

milpro011's avatar
milpro011
Helper I
7 years ago

Matrix Table Sub Totals NOT Computing for a Measure

Hi, 

I have the following Measure devised to compute dynamic buckets: 

PlaceBucketMP = 
SUMX(
    ADDCOLUMNS(
        PlaceBuckets,
        "BucketsTotal",
        COUNTROWS( 
            FILTER(    
                ADDCOLUMNS(
                    DISTINCT(Student_Place_History[PlaceAR]),
                    "PlacedTotal",
                    Calculate(
                        SUM(Student_Place_history[Students]),
                        DISTINCT(Student_Place_History[PlaceAR]),
                        ALLSELECTED()
                    )
                ),
                COUNTROWS(
                    FILTER(
                        'PlaceBuckets',
                        [PlacedTotal] >= PlaceBuckets[Min]
                        && [PlacedTotal]<=PlaceBuckets[Max]
                    )
                )>0
            )
        )
    ),
    [BucketsTotal]
)

It seems to be working, however, I can't get the subtotals to compute in the Matrix - see below:

 

This is the "Place Buckets" table:

 

Many thanks!

Need Help

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi milpro011 ，

     

    You can try to create measure PlaceBucketMP_New, assuming the [Students] in measure is the field that you have masked in screenshot.

     

    PlaceBucketMP_New=

    VAR _table = SUMMARIZE('Student_Place_history',[Students] ,"_Value",[PlaceBucketMP])

    RETURN

    IF(HASONEVALUE([Students]),[PlaceBucketMP],SUMX(_table,[_Value]))

     

    Best Regards,

    Amy

     

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

    • milpro011's avatar
      milpro011
      Helper I
      7 years ago

      v-xicai Many thanks!

      The blacked out items are PlaceARs.

       

      This is the new measure that I created: 

      PlaceBucketTot = 
VAR _table = SUMMARIZE(Student_Place_History,[PlaceAR], "_Value", [PlaceBucketMP])
RETURN IF(HASONEVALUE(Student_Place_History[PlaceAR]),[PlaceBucketMP],SUMX(_table,[_Value]))

      But I got this error:

      The query referenced calculated column 'Student_Place_History'[PlaceBucket] which does not hold any data because there is an error in its expression.

       

      What I'm trying to achieve is to figure out how many [PlaceARs] do we have that have "Placed" more than 7 students, between 7 and 14, as per the "PlaceBuckets" table provided above. 

       

      Once again, thank you for helping me. 

      • milpro011's avatar
        milpro011
        Helper I
        7 years ago

        v-xicai I actually changed the second measure to point to the bucket table, and it returned no errors, yet it is still not summing up. 

         

        PlaceBucketTot = 
VAR _table = SUMMARIZE(PlaceBuckets,[Placements], "_Value", [PlaceBucketMP])
RETURN IF(HASONEVALUE(PlaceBuckets[Placements]),[PlaceBucketMP],SUMX(_table,[_Value]))

        Thanks!