this is a tough one

[Anonymous]

hello everyone 

here is my challenge 

i am trying to create a measure that allows me to evaluate a result for a bunch of expenses in a waterfall and only show the top 3 and bottom 5,  then consolodate all of the other expesnses into a formula called "other expenses 

here is what i have in the dax formula so far need so jedi master help 🙂

Test 2 =
VAR SelectedValue =
CALCULATE (
[Test 1],
INTERSECT (
VALUES ( 'DIM_Account_Hierarchy - NI'[L6] ),
VALUES ( 'OPEX Detail'[L6] )
)
)
VAR UnselectedValue =
CALCULATE (
[Test 1],
EXCEPT (
ALL ( 'DIM_Account_Hierarchy - NI'[L6] ),
VALUES ( 'DIM_Account_Hierarchy - NI'[L6] )
)
)
VAR AllValue =
CALCULATE ( [Test 1], ALL ( 'DIM_Account_Hierarchy - NI'[L6] ) )
VAR RowCount =
COUNTROWS ( 'OPEX Detail' )
VAR TableOPEX =
ADDCOLUMNS ( 'OPEX Detail', "OPEXWalk", [Test 1] )
VAR RankOpex =
ADDCOLUMNS ( TableOPEX, "Rank", RANKX ( TableOPEX, [Test 1],, ASC ) )
VAR isAllOther =
ADDCOLUMNS (
RankOpex,
"NewExpenseName", IF ( [Rank] >= 6 && [Rank] < RowCount - 3, "All Others", 'OPEX Detail'[L6] )
)
VAR CalcTable =
CALCULATETABLE (
SUMMARIZE (
FILTER ( RankOpex, [Rank] >= 6 && [Rank] < RowCount - 3 ),
'OPEX Detail'[L6]
)
)
RETURN
CALCULATE (
IF (
HASONEVALUE ( 'OPEX Detail'[L6] ),
SWITCH (
VALUES ( 'OPEX Detail'[L6] ),
"All Others", UnselectedValue,
SelectedValue
),
AllValue
),
FILTER (
'DIM_Account_Hierarchy - NI',
'DIM_Account_Hierarchy - NI'[L6] = CALCULATETABLE ( CalcTable )
)
)

[v-yingjl · Accepted solution]

Hi @Anonymous ,

I hope this sample file can help you to refer: top3 and bottom5.pbix 

Create a table manually:

Here are the main measures:

Sales Amount Top3 = 
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        TOPN(
            3,
            ALL('Table'[Invoice No.]),[Total Sales],DESC
        )
    )
)

Sales Amount Bottom5 = 
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        TOPN(
            5,
            ALL('Table'[Invoice No.]),[Total Sales],ASC
        )
    )
)

Sales Amount Other = 
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        EXCEPT(
            ALL('Table'[Invoice No.]),
            TOPN(
                3,ALL('Table'[Invoice No.]),[Total Sales],DESC
            )
        )
    ),
    KEEPFILTERS(
        EXCEPT(
            ALL('Table'[Invoice No.]),
            TOPN(
                5,
                ALL('Table'[Invoice No.]),
                [Total Sales],ASC
            )
        )
    )
)

Sales Amount Top & Others = 
IF(
    HASONEVALUE('Top & Other'[Top]),
    SWITCH(
        VALUES('Top & Other'[Top]),
        "Top3",[Sales Amount Top3],
        "Bottom5",[Sales Amount Bottom5],
        "Other",[Sales Amount Other Total Only]
    )
)

Best Regards,
Yingjie Li

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

[Greg_Deckler]

@Anonymous - Have you investigated "binning" in Power BI?

Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.