Forum Discussion

PowerBITestingG's avatar
PowerBITestingG
Icon for Resolver I rankResolver I
3 years ago

Alternative to allselected to sum rows with same value

Hello,

I am trying to create a measure (Available total)  that sums rows if the Country is the same . Any ideas?

 

DCDC TypePrimary SourceSourceCountryAvailableAvailable total
IN03COUNTRYUS03 India23151.4
IN04COUNTRYUS03 India128.4151.4
CN03COUNTRYUS03 China74327
CN05COUNTRYUS03 China14404327
CN02COUNTRYUS03US03China28804327

This the formula that I have but as it uses ALLSELECTED and I need to use it then in an iterator so its giving me values in other measures that dont make sense

 

Available total = 
var AVA_summary = 
    SUMMARIZE (ALLSELECTED(DCList),
        DCList[DC & ProductCd],
        DCList[ProductCD&Country],
        DCList[Source],
        DCList[DC])
return
SUMX(AVA_summary,[Available])

 

(calculated column wont work because of it requieres user input variables)

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    PowerBITestingG Hard to tell exactly what is going on here without more information. But generally I would expect something like this:

    Available Total
  VAR __Country = MAX('DCList'[Country])
  VAR __Table = GROUPBY('DCList',[Country],"__Value",SUMX(CURRENTGROUP(),[Available]))
  VAR __Result = MAXX(FILTER(__Table,[Country] = __Country),[__Value])
RETURN
  __Result
    • PowerBITestingG's avatar
      PowerBITestingG
      Icon for Resolver I rankResolver I
      3 years ago

      Greg_Deckler 

       

      I get an error. "The calculate function cannot be used in an expression argument for the GROUPBY() function. I should have specified that Available is already a measure summing measures.