Show base data of measure

Question

I've a table in power BI containing information on recipes and ingredients as shown below:

I need to get only amount of ingredients, which appear in at least 2 recipes, this part I can manage with measures:

CountRecipe = DISTINCTCOUNT('Table'[recipe])
FilteredAmount = if([CountRecipe]>=2,SUM('Table'[amount]))

What I can't solve is to show also the recipes in the last matrix ("total amount of filtered ingredients"), here is the desired output prepared in Excel:

I've tried to solve it calculating results in a new table, however I've also connected tables filtering my data and calculated table just contained everything. So I suppose I need a measure which respect user filtering too.

Editable data:

| recipe | ingredient | amount | count |
|--------|------------|--------|-------|
| R1     | I1         | 13     | 1     |
| R1     | I2         | 32     | 3     |
| R1     | I3         | 14     | 2     |
| R2     | I2         | 11     | 3     |
| R2     | I3         | 29     | 2     |
| R2     | I4         | 17     | 1     |
| R2     | I5         | 49     | 2     |
| R3     | I5         | 40     | 2     |
| R4     | I2         | 17     | 3     |
| R5     | I6         | 39     | 1     |
| R5     | I7         | 14     | 1     |
| R5     | I8         | 32     | 1     |

Angelo Canepa Angelo Canepa · Accepted Answer · 2020-10-19T11:16:59

I will use the column count in your table. I have just recalculated just in case in the step add_count_col.

You can skip this step if you trust your own calculation.

FilteredAmount = 
VAR add_count_col =
    ADDCOLUMNS (
        'Table',
        "col_count_recipe",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[recipe] ),
                ALLSELECTED ( 'Table'[recipe] ),
                FILTER ( ALL ( 'Table' ), [ingredient] = EARLIER ( 'Table'[ingredient] ) )
            )
    )
VAR ingredienttable =
    FILTER ( add_count_col, [col_count_recipe] >= 2 )
VAR byingredient =
    CALCULATE ( SUM ( 'Table'[amount] ), ingredienttable )
RETURN
    byingredient

Here is my output:

Show base data of measure

2 Answers