Select one of two dimensions then sort by TopN on one Chart

[QN278]

Hi Power BI Experts,

I am new to Power BI. I have two dimensions - Product Estimated price and Product Actual price from different source.

I am trying to create a chart use TopN to show those two dimensions. Following is my TopN(Actual Price) DAX-

TopN(Actual Price) =

VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])

RETURN

SWITCH(TRUE(),

    SelectedTop = 0, [Actual Price],

    RANKX (

            ALLSELECTED( 'Product'[ProductName] ),

            [Actual Price]

                )

                  <= SelectedTop,

        [Actual Price]

)

But the result not I wanted. You can see the chart shows the top2 of these two dimensions.

The Product A 's Estimated price is missing.Because for A, its Estimated price is not among the top two.

My desired result is that when I select one of the dimensions,

The chart can display these two prices of the product based on TopN.

Is it possible to select one of them first then sort by TopN on one Chart ?

Thank you. 

[Anonymous · Accepted solution]

Hi @QN278 ,

You can try step like below:

create two slicer table:

create below measure:

M_actual = 
var actual_ = CALCULATE (
    MAX ( Actual[Products] ),
    KEEPFILTERS (
        TOPN ( SELECTEDVALUE ( 'TopN'[Topn] ), ALL ( Actual ), Actual[Prices] )
    )
)

return if(MAX(Estimated[Products]) = actual_ ,MAX(Estimated[Price]),BLANK())

M_estimated = 
var estimated_ = CALCULATE (
    MAX ( Estimated[Products] ),
    KEEPFILTERS (
        TOPN ( SELECTEDVALUE ( 'TopN'[Topn] ), ALL ( Estimated ), Estimated[Price] )
    )
)
return if(MAX(Actual[Products]) = estimated_ , MAX(Actual[Prices]),BLANK())

M1 = CALCULATE (
    MAX ( Actual[Prices] ),
    KEEPFILTERS (
        TOPN ( SELECTEDVALUE ( 'TopN'[Topn] ), ALL ( Actual ), Actual[Prices] )
    )
)

M2 = CALCULATE (
    MAX ( Estimated[Price] ),
    KEEPFILTERS (
        TOPN ( SELECTEDVALUE ( 'TopN'[Topn] ), ALL ( Estimated ), Estimated[Price] )
    )
)

result1_ = 
var cur_ = SELECTEDVALUE(slicer[slicer])
return SWITCH(cur_, "Actual",[M1], "Estimated",[M_estimated])


result2_ = 
var cur_ = SELECTEDVALUE(slicer[slicer])
return SWITCH(cur_, "Actual",[M_actual], "Estimated",[M2])

Best Regards,
Adamk Kong

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

[QN278]

I found a way to solve my problem, first drag my expected field (For example : actual price) to product names fielter value on the chart. Then put these two dimensions into the Y-axis chart and it was done. The only problem is that I can't dynamically adjust the value of TopN.

[Anonymous]

Hi @QN278 ,

Please provide test data and screenshot of desired results.

Best Regards,
Adamk Kong

[QN278]

Hi  @v-kongfanf-msft  ,

I can't find a place to upload pbix data.
So I upload the picture.
I have 3 different sources. They use product names to manage relationship.
I input some test data.
My desire results is when i select estimate filter.The chart show as picture 1.
And when i choose another one actual filter.The chart show as picture 2.

RelationshipTest data12

[Anonymous]

Hi @QN278 ,

You can create the following formulas by selecting one of them first and then sorting them by TopN on a chart.

SelectedMeasure = 
VAR SelectedMeasure =
    SELECTEDVALUE ( 'Slicer'[MeasureSelection] )
RETURN
    SWITCH (
        SelectedMeasure,
        "Estimated Price", [Measure_estimate],
        "Actual Price", [Measure_actual]
    )

Best Regards,
Adamk Kong

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

[QN278]

Hi  @Anonymous 

Thank you for your help.

Is it possible to sort the TopN at the same time on one chart after selecting one of measure?

For example if i choose Estimated price measure about Top5.

The chart should show the expected prices of the products base on Top5, then their actual price.
The purpose of displaying them simultaneously is to facilitate comparison.