Measure of respective bins

[UsePowerBI]

Hello

I have this data:

Prod	Metric	Timestamp	Value	Concat	Timestamp (bins)
A	Metric Basis	27/11/2021 00:00	4	A - Metric Basis	27/11/2021
A	Metric 1	27/11/2021 00:30	1	A - Metric 1	27/11/2021
A	Metric 1	27/11/2021 00:31	2	A - Metric 1	27/11/2021
A	Metric 1	27/11/2021 00:32	9	A - Metric 1	27/11/2021
A	Metric 1	27/11/2021 00:33	10	A - Metric 1	27/11/2021
B	Metric Basis	27/11/2021 00:00	8	B - Metric Basis	27/11/2021
B	Metric 1	27/11/2021 00:30	5	B - Metric 1	27/11/2021
B	Metric 1	27/11/2021 00:31	6	B - Metric 1	27/11/2021
B	Metric 1	27/11/2021 00:32	13	B - Metric 1	27/11/2021
B	Metric 1	27/11/2021 00:33	14	B - Metric 1	27/11/2021

I want to create a line graph per 'Timestamp' with a Slicer using 'Concat' and I want in the Y axis to show:

'Value' of the selected 'Concat' of the respective 'Timestamp' divided by 'Value' of the 'Metric Basis' of same 'Prod' (from the selected 'Concat') of the respective 'Timestamp (bins)'

So for example, if I choose in the slicer = 'B - Metric 1', the X,Y axes would be:

27/11/2021 00:30, 5/8
27/11/2021 00:31, 6/8
27/11/2021 00:32, 13/8
27/11/2021 00:33, 14/8

Any idea how the Measure should be?

Thanks!

[v-zhangti · Accepted solution]

Hi, @UsePowerBI 

You can try the following methods.

Measure = 
VAR n1 =
    SELECTEDVALUE ( 'Table'[Value] )
VAR n2 =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Metric] = "Metric Basis"
                && [Prod] = MAX ( 'Table'[Prod] )
        )
    )
RETURN
    DIVIDE ( n1, n2 )

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[bcdobbs]

I think this should work:

PlotValue = 

VAR CurrentValue = SELECTEDVALUE( MainTable[Value] )

VAR BasisValue =
    CALCULATE (
        SELECTEDVALUE ( MainTable[Value] ),
        REMOVEFILTERS ( MainTable ),
        VALUES ( MainTable[Prod] ),
        VALUES ( MainTable[Timetamp (bins)] ),
        MainTable[Metric] = "Metric Basis" 
    )

RETURN
    DIVIDE ( CurrentValue, BasisValue )

The key logic is in the CALCULATE for BasisValue. It removes all filters from your table (I called it MainTable). Then puts back any existing filters on the Prod and Timetamp (bins) columns. Finally it adds a filter to only get "Metric Basis".