How to sum certain rows if dimension contains certain text? (Not in table calcs)

[ryangifford]

I have some data that looks like this:

Group                      Name                      Price

70450                   Facility                     $700

70450                   Professional           $68

70450                   Misc.                        $78

80000                   Facility                     $850

80000                   Professional           $70

80000                   Misc.                        $109

I want to create a dimension or measure that will sum the price of rows where “Name” contains “Facility” or “Professional” and retain the grouping. The resulting table would look like this:
 

Group                      Name                      Price

70450                   Facility                     $768

70450                   Professional           $768

70450                   Misc.                        $78

80000                   Facility                     $920

80000                   Professional           $920

80000                   Misc.                        $109

Not sure how to approach this or if this is even possible. Thanks in advanced for the help.

[IanT]

new measure:

type: sum

sql: case when name like facility or name like professional then price end

[ryangifford]

@IanT Thanks for the reply. This works only if I remove the “name” dimension. Is there a way to keep all the dimensions and rows there and still aggregate select rows?

[IanT · Accepted solution]

Ah right sorry I didnt look at your example data to be fair.

In this case you will need a derived table or a native derived table to calculate this measure independently by groupid and then join it on groupid (excluding the Name as you say).

The data you want is at a different aggregation to what you want to display so its the only way….unless you want to fiddle around and create what I think is a nasty table calc.

[ryangifford]

Thank you, that makes sense! I’ll give this a go.