Forum Discussion

amikm's avatar
amikm
Helper V
5 years ago

Matrix visual is not summing up

I have a matrix visual. I have one measure which is showing 1 or blank as per business logic. I want to show the roll-up of those values in the hierarchy.
I wrote the below DAX to achieve the same but It's not giving the correct value. Ideally, the value should be 8
Measure = IF(HASONEFILTER('CAM Alignment'[FullName_L1]) &&

HASONEFILTER('CAM Alignment'[FullName_L2])&&
HASONEFILTER(Validation_Accounts_Agreements[CalculatedAccountNameNumber]),
if([MeasureCappedCountSolution]=1,1,blank()),
CALCULATE(COUNTROWS(DISTINCT(Validation_Accounts_Agreements[CalculatedAccountNameNumber])),FILTER(SFDC_Opportunity_NBV,[MeasureCappedCountSolution]=1)))
 
Need Help

6 Replies

  • tex628's avatar
    tex628
    Community Champion
    5 years ago

    The integrated totals is not actually summarizing the rows above but instead calculating the same measure without any of the rowcontext in your matrix.

    Try this and replace the referece to "YourDimension" to the lowest row dimension that you are using in your matrix:

    Measure 2 =
SUMX(
SUMMARIZE(
'Table'[YourDimension] , 
"Value" , [MeasureCappedCountSolution]
) , [Value]
)


    Br,
    J

     

    • amikm's avatar
      amikm
      Helper V
      5 years ago

      Hi @tex628
      I created the measure (Measure_test), what you have described,
      but I am not getting a roll up the sum, I am expecting 8 as a sum when we do rollup in the hierarchy.
      DAX2.png

      • tex628's avatar
        tex628
        Community Champion
        5 years ago

        Can you provide the code for the MeasureCappedCountSolution measure? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amikm 

    I build an easy sample to have a test and I use ISINSCOPE function to achieve your goal.

    And I build two measures at first.

    MeasureSale = SUM('Table'[Sales])
    MeasureCappedCountSolution = IF(NOT([MeasureSale] in {2,4}),1,BLANK())

    Then I build a new measure and the result is as below.

    Measure = IF(ISINSCOPE('Table'[LEVEL3]),[MeasureCappedCountSolution],SUMX(ALL('Table'),[MeasureCappedCountSolution]))

    Result is as below. The measure works well.

    You can download the pbix file from this link: Matrix visual is not summing up

     

    Best Regards,

    Rico Zhou

     

    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

      Hi as per our requirement the AVP,RVP are coming from differnt table (let say hierarchy), the measurecount is coming from validation table. Now as per your formula could you please help what table we need to take in ALL function.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        Could you provide me with your table samples? This may make it easier for me to understand your data model.

         

        Best Regards,

        Rico Zhou

         

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