Forum Discussion

Brenda_Loznik's avatar
Brenda_Loznik
Advocate II
3 years ago
Solved

Summarize while ignoring context

Hi,

 

I'm trying to create a measure that sums FCT_Contract[NumberOfContracts] for each Dim_Customer[HoldingName], but only when there is a holding (i.e.  HoldingName <> Blank()).

 

I have tried this measure, but because it is responds to the values of CustomerName, I don't get my desired output. So, I basically get a duplicate of the 'NumberOfContracts' column, but with a blank space for the customers that are not part of a holding. I have tried playing with ALL and ALLEXCEPT, but I haven't quite figured it out.

 

How can I create a measure that ignores the CustomerName?

 

NumberOfContractsHolding =
var summarizedtable = Summarize(
    filter(FCT_Contract, related( DIM_Customer[Holdingname]) <> Blank()),
    DIM_Customer[Holdingname])
    
var result = sumx(summarizedtable ,[NumberOfContracts])
return result

 

 

 

Need Help
  • eliasayyy's avatar
    eliasayyy
    3 years ago

    its ok well im not sure about your dataset but i replicated just the table you provided above 


    create the new measure

    Total by Holding = 
CALCULATE([Number of Contracts],ALLEXCEPT('Table','Table'[HoldingName]),'Table'[Holding] = 1)

     

     

     

3 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago

    so you do not want the 0 right ? 

     

    NumberOfContractsHolding =
var summarizedtable = Summarize(
    filter(FCT_Contract, AND(related( DIM_Customer[Holdingname]) = 1 , related( DIM_Customer[Holdingname]) <> Blank()),
    DIM_Customer[Holdingname],"Amount" , [NumberOfContracts])
    
var result = sumx(summarizedtable ,[Amount])
return result

     

    • Brenda_Loznik's avatar
      Brenda_Loznik
      Advocate II
      3 years ago

      Hi,

      Thanks for your time.

      I actually want to create the red numbers in my table (I probably should have used a different color...). 

       

      I expect that different customers belonging to the same holding will receive the same output, but unfortunately this is not the case. 

      Unfortunately this Dax measure also doesn't quite aggregate the date the way I would like.

       

      • eliasayyy's avatar
        eliasayyy
        Memorable Member
        3 years ago

        its ok well im not sure about your dataset but i replicated just the table you provided above 


        create the new measure

        Total by Holding = 
CALCULATE([Number of Contracts],ALLEXCEPT('Table','Table'[HoldingName]),'Table'[Holding] = 1)