[ TOPN helping with measure and understanding ]

[bolabuga]

Hello everyone,

Im trying to understand why my measure with topn ASC is not working, and failing to understand why it isnt working.

Its been used in a context with a slicer for yearmonth (datetable) and a matrix visual with "activity branch code column" in the row. 

Topn Desc is working OK.

VOLUME TOPN DESC = 
CALCULATE(
    [TOT VOLUME];
    KEEPFILTERS(
        TOPN(
            5;
            ALLSELECTED(BASE_FORUMS_V2[ACTIVITY_BRANCH_code]);
            [TOT VOLUME];
            DESC)))

By understanding point of view, is my description wrong??

- Measure will be evaluated each row of the matrix

- Allselected will remove the filter from "activity branch colum", so all branch are visible for any row in the matrix

- Context transition will happen because of the "tot volume" measure, while evaluating each row of the table returned from "allselected", turning each branch code row into a valid filter.

- Keepfilters will intersect the table returned by "Allselected" with the current filter context, which will, for the rows inside the matrix, return just the value for a "activity branch" when it is on the top 5 values and for the grandtotal will return a table with the top 5 branchs which will be summed by the measure in the calculate.

The measure is working well, and when slicing by yearmonth, it is returning top 5 best results descending for that selected yearmonth.

Top N Asc is returning BLANK results for some of the selections in yearmonth, and returns NUMBER results with no selection in yearmonth.

VOLUME TOPN ASC = 
CALCULATE(
    [TOT VOLUME];
    KEEPFILTERS(
        TOPN(
            5;
            ALLSELECTED(BASE_FORUMS_V2[ACTIVITY_BRANCH_code]);
            [TOT VOLUME];
            ASC)))

I have uploaded a pbix file with the measures and the context.

https://drive.google.com/open?id=1ZZ9UxbuABQVy-97yEFnp4o1LcITTLx5t 

[amitchandak]

@bolabuga , refer if these 2 can help

https://databear.com/power-bi-dax-topn-function/

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[bolabuga]

@amitchandak 

Thks for the links, they are really helpful and give me lots of new possibilities.

Adding a visual ranking number with RANKX helped me seeing that the worst results are indeed BLANK, then the measure for the TOPN ASC is indeed returning blanks for some of the yearmonth selections, because the worst branchs have no computed result at all.

I just want to be sure that im not misunderstanding things, which raises another question:

Is blank the TOP ASC value returned from "TOPN" function, even when theres ZEROs between possible worst results?? 

After more tests i can clearly see that when i have 5 blanks results or more, no zero is showed and when i have 4 or less blanks, then all the zero results are visible as ties, filling the positions on top 5 asc.

I would like to add a measure that will show the worst results that are different than zero's and blank's.

VOLUME TOPN ASC EXCLUDING BLANK OR ZEROS = 
CALCULATE(
    [TOT VOLUME];
    KEEPFILTERS(
        TOPN(
            5;
            FILTER(
                ADDCOLUMNS(
                    CALCULATETABLE(
                        VALUES(BASE_FORUMS_V2[ACTIVITY_BRANCH_code]);
                        ALLSELECTED(BASE_FORUMS_V2[ACTIVITY_BRANCH_code]));
                    "@VOLUME" ; [TOT VOLUME]);
                [@VOLUME] <> BLANK());
            [@VOLUME];
        ASC)))