Matrix visual is not summing up

[amikm]

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)))

[Anonymous]

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]

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]

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. 

[tex628]

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]

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.

[tex628]

Can you provide the code for the MeasureCappedCountSolution measure?