Forum Discussion

nh27's avatar
nh27
Helper III
2 years ago
Solved

Calculated column for distinct values

Bit of a challenging one I'm finding myself here, please see the attached image/table.

 

I have a Gross Value column but I actually need to calculate the GR Amount and IR Amount, the problem is the raw data duplicates Gross Value.

For GR Amount, Column HCT = E
For IR Amount, Column HCT = Q

Is there a way I can use DAX and is it smart enough to calculate the sum of GR Amount based on each 'Item' column?

So for above example the sum I'm actually looking to return for GR Amount is 19,882 and 17,212 for IR Amount.

Any ideas?

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    2 years ago

    nh27 

    It's better to create measures as follows, create the same for B as well

     

    Gross Value E = 

CALCULATE(
	SUMX(
		SUMMARIZE(
			TABLE07,
			Table07[HCT],
			Table07[Gross Value]
		),
		Table07[Gross Value]
	),
	Table07[HCT] = "E"
)

     



3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    nh27 

    It's better to create measures as follows, create the same for B as well

     

    Gross Value E = 

CALCULATE(
	SUMX(
		SUMMARIZE(
			TABLE07,
			Table07[HCT],
			Table07[Gross Value]
		),
		Table07[Gross Value]
	),
	Table07[HCT] = "E"
)

     



  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    nh27 Not sure I am 100% following but maybe something like this:

    GR Amount Measure =
  VAR __Table = SUMMARIZE( FILTER( 'Table', [HCt] = "E" ), [Item], "__Gross", AVERAGE([Gross value]))
  VAR __Result = SUMX( __Table, [__Gross] )
RETURN
  __Result
  • nh27's avatar
    nh27
    Helper III
    2 years ago

    Thank you both, I used your example Fowmy  and it worked a treat