Aggregate of measures at different filter level - PLEASE HELP!

[snow_man]

Hi all,

I have a question related to measures in Power BI desktop. I have a table visualisation which shows a measure for each unique combination of identifiers (1, 2, & 3). (Each identifier 1 can take many values for identifier 2, which can take many values for identifier 3.)

My question is how do I aggregate the measure to find the max (or min) for a given value of identifier 1 and 2? An example is shown here of what I'm trying to achieve. The first table looks like this:

I would like a second table which evaluates the max and min at the identifier 2 level:

Any help would be much appreciated. I'm sure this must be possible with measures but this goes beyond my understanding and it's hard to articulate using a search engine.

Thanks!

[Anonymous]

Hi @snow_man ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new measure to find MAX.

MaxValue = CALCULATE(MAX('Table'[Me]), ALLEXCEPT('Table','Table'[Identifier1],'Table'[Identifier2]))

3.Create the new measure to find MIN.

Minvalue = CALCULATE(MIN('Table'[Me]), ALLEXCEPT('Table','Table'[Identifier1],'Table'[Identifier2]))

4.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[snow_man]

Hi @Anonymous,

That didn't work. When I try to create the measure it says 'Column 'Measure' in table 'Table' cannot be found or may not be used in this expression.' I assume because this is a measure to begin with, not a column. 

Just to be clear, the first table in my question is a table visual, not a data table.

[Anonymous · Accepted solution]

Hi @snow_man ,

1.Recreate the origin table and calculate the measure.

Measure = 
VAR TrueStepsCount =
CALCULATE(
    COUNTROWS('Tableorigin'),
    FILTER(
        'Tableorigin',
        'Tableorigin'[True/False] = TRUE()
    )
)
VAR TotalStepsCount = COUNTROWS(Tableorigin)

RETURN TrueStepsCount / TotalStepsCount

2.Create the new measure to filter.

MAX Measure = MAXX(ALLEXCEPT('Table','Table'[Identifier1],'Table'[Identifier2]), Tableorigin[Measure])

MIN Measure = MINX(ALLEXCEPT('Table','Table'[Identifier1],'Table'[Identifier2]), Tableorigin[Measure])

3.Drag the measures into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[snow_man]

Hi @Anonymous ,

This would work if there were 2 tables ('Tableorigin' and 'Table') but there is only one table ('Tableorigin'). The second 'Table' is only a visual.

Is there any way around this? e.g. by creating a new table from Tableorigin?

[ryan_mayu]

is this a table visual? if yes,pls provide the raw data.

[snow_man]

Hi @ryan_mayu,

Yes the first table is a visual, with the measure being evaluated from the original data table. The raw data table looks like this:

The measure is as follows. Essentially it's giving you the proportion of 'TRUE' entries in the raw data table (which is evaluated per unique combination of identifiers 1,2&3 in the table visual).

Proportion =

VAR TrueStepsCount =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[TrueFalse] = TRUE()
    )
)
VAR TotalStepsCount = COUNTROWS(Table)

RETURN StepsCount / TotalStepsCount

I hope this helps clear things up. I look forward to your response.