Sum values of a measure in a hierarchy

[ClearRide]

Here is a segment of my data in a matrix with a hierarchy. Using 2 tables: Sales and Organization.
I want to sum the values of the measure [NF CountPlp Over Quota]. For example, Kelly Henrich should be 6 (not 1). Code for measure: 

IF([NF Over Quota] >=0 , 1, 0)

For the sake of the question, I will also leave what I have for the related measures since they might be apart of the solution: 

[NF Over Quota] = [Total NF] - [NF Over Quota]
[NF Quota] = SUM(Organization[NFQuota])

[Total NF] = CALCULATE(SUM('Sales'[NF Sold]), FILTER(Sales, Sales[Category] = 7))

I tried creating a calculated column with the same code IF([NF Over Quota] >=0 , 1, 0) and summing that, but it didn't work (got all 1s for some reason). I couldn't do SUM ([NF Over Quota]) because it's not a column.

Please let me know if you need more information, thank you.
Edit: Here is the pbix https://drive.google.com/file/d/1IHv7iVGpX7wUzrGO8rcu63_KJBhZ9erc/view?usp=share_link

[Anonymous · Accepted solution]

Hi  @ClearRide ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table1=SUMMARIZE('Sales','Sales'[DSR],"Value",[NF CountPlp Over Quota])
return
IF(
    ISINSCOPE('Sales'[DSR]),[NF CountPlp Over Quota],SUMX(_table1,[Value])

2. Result:

Best Regards,

Liu Yang

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

[sevenhills]

You need to use in your calculation HASONEFILTER and ISINSCOPE.

We dont have your data model sample, so please refer to this article as it explains clearly.

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[ClearRide]

Thank you for letting me know, I have provide the pbix for a closer look.

[sevenhills]

Use this one and see if it works.

NF CountPlp Over Quota = 
SWITCH (
  TRUE (),
  ISINSCOPE(Sales[DSR]), IF([NF Over Quota] >=0 , 1, 0),  -- you can use HASONEFILTER, ISFiltered also
    SUMX( 
         SUMMARIZE(Sales, Sales[DSR] -- field used in the matrix
                , "To Sum Over Quota", IF([NF Over Quota] >=0 , 1, 0))
         , [To Sum Over Quota])
)

I renamed your old measure as "NF CountPlp Over Quota Issue"

[sevenhills]

Same can be achieved similarly as below:

NF Count Plp Over Quota 2 = 

var _t = FILTER( 
            SUMMARIZE( 'Sales', Sales[Division], Sales[DSM], Sales[DSR], 
                  "NF Quota", [NF Quota], 
                  "Total NF", [Total NF]) 
            , [Total NF] - [NF Quota] >= 0
            )

return  IF (HASONEFILTER(Sales[DSR]), 
              IF([Total NF] - [NF Quota] >=0 , 1, 0), 
              COUNTROWS(_t) 
        )

-- Below line also works
-- return  IF (HASONEFILTER(Sales[DSR]), IF([Total NF] - [NF Quota] >=0 , 1, 0), sumx(_t, IF([Total NF] - [NF Quota] >=0 , 1, 0)) )