need to use summarize, calculate, or group?

[Anonymous]

I have been trying to use a few different ways to get the following output for the data model (see link below).

I'm having trouble traversing the model and aggregating appropriately.

I want to calculate the min, max and range for each combination of widget, source, metric name. And also have a value for the rank of that combination of items. Here is the kind of output i'm looking for.

Here is the PBIX: https://1drv.ms/u/s!Ap90MsT-kD-eg1jlS3wFzk7r0UEA?e=EbFcDY

[v-zhangti · Accepted solution]

Hi,@JohnKing

You can try the following methods, one is to calculate columns, the other is to measure.

1. Calculated column

MAX =
CALCULATE (
    MAX ( 'Values'[MetricValue] ),
    FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)

MIN =
CALCULATE (
    MIN ( 'Values'[MetricValue] ),
    FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)

Range =
[MAX] - [MIN]

Rank of Range =
IF (
    [Range Measure] = BLANK (),
    BLANK (),
    RANKX ( 'Widgets', [Range Measure],, DESC )
)

The results are shown in the figure.

   2. Measure

MAX Measure =
CALCULATE (
    MAX ( 'Values'[MetricValue] ),
    FILTER (
        ALL ( 'Widgets-Sources' ),
        [Widgets.Index] = MAX ( Widgets[Widgets.Index] )
    )
)

MIN Measure =
CALCULATE (
    MIN ( 'Values'[MetricValue] ),
    FILTER (
        ALL ( 'Widgets-Sources' ),
        [Widgets.Index] = MIN ( Widgets[Widgets.Index] )
    )
)

Range Measure =
[MAX Measure] - [MIN Measure]

Rank Measure =
IF (
    [Range Measure] = BLANK (),
    BLANK (),
    RANKX ( ALL ( 'Widgets' ), [Range Measure],, DESC )
)

The results are shown in the figure.

Best Regards,

Community Support Team _Charlottez 

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

[Anonymous]

Thanks Amitchandak
This is close. For some reason the Rank is not quite getting picked up.

I have updated the pbix file in the original link above.

Also, I may submit a follow up question regarding having the min, max, and range values set up so that they are only calculated at that summarized level. That would allow me to get e.g.,  an average range rather than the range of whole set. I'll make that a new submission. 

[amitchandak]

@Anonymous ,

You need measures like

Min = min(Value[Metric Value])

Max = max(Value[Metric Value])

Range = [Max] - [Min]

Rank = Rankx(allelected(Values), [Range],,desc)

or

Rank = Rankx(summzrize(allselected(Values), Widget[WidgetName]), Label[Lable]), , [Range],,desc)