Trying to access table variable fields in return command and count rows in the filtered table.

[rrr]

Hi Everyone,

Table template:

ID	Product	Amount
1	FOODS	2000
2	Fertilizer	5000
3	Livestock	5000
4	Unallocated	6000
5	FOODS	7000
3	Fertilizer	2000
7	Livestock	5000
8	Unallocated	5000
3	FOODS	6000
4	Fertilizer	7000
1	Livestock	2000
1	Unallocated	5000
13	Unallocated	5000

I am trying to create a measure where I need to count the number of IDs who contribute to 20% of total amount in descending order.

I created the below dax, everything works fine in Dax studio but  the result is different when implemented in Power BI, checking if someone can help in fixing it.

Below is the detailed description of what i am trying to do.

GM Cumulative =

-- Creating a summary of amount grouped by ID and Product 

------------------------------------------------------------------------------------------------------------------------------------------------
VAR tbl =
    SUMMARIZE (
        CALCULATETABLE (
            FILTER (
                ALL ( Leaderboard ),
                Leaderboard[EOM] > [Date Start]  
                    && Leaderboard[EOM] <= [Date End]  -- Date Start and Date END End changes based on the selcted value being MTD/YTD/R12
                    && LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED"
            ),
            CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
        ),
        'Leaderboard'[Employee  ID],
        'Leaderboard'[Product Division],
        "SUM_AMT", SUMX ( Leaderboard, 'Leaderboard'[GROSS_MARGIN_AMOUNT] )
    )

----------------------------------------------------------------------------------------------------------------------------------------------

-- Table two ranking the summed value and sorted in Descending order
VAR tbl2 =
    ADDCOLUMNS (
        tbl,
        "rank",
            RANKX (
                SUMMARIZE (
                    CALCULATETABLE (
                        FILTER (
                            ALL ( Leaderboard ),
                            Leaderboard[EOM] > [Date Start]
                                && Leaderboard[EOM] <= [Date End]
                                && LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED"
                        ),
                        CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
                    ),
                    'Leaderboard'[Product Division],
                    'Leaderboard'[Employee  ID],
                    "SUM_AMT", SUM ( 'Leaderboard'[GROSS_MARGIN_AMOUNT] )
                ),
                [SUM_AMT],
                ,
                DESC,
                SKIP
            )
    )

--------------------------------------------------------------------------------------------------------------------------------------------

-- Table 3 is creating a new column with cumulative sum based on Rank ( 1st row will have 1st rank value, 2nd row will have summed up values of rank 1 and 2 and so on..)
VAR tbl3 =
    ADDCOLUMNS (
        tbl2,
        "Cum_Sum", SUMX ( TOPN ( [rank], tbl2, [sum_amt], DESC ), [Sum_AMt] )
    )

---------------------------------------------------------------------------------------------------------------------------------------

--Table 4 summing up the total value of amount to use in return (to compare 20% of it with Cumulative sum)
VAR tbl4 =
    ADDCOLUMNS ( tbl3, "Total_Sum", SUMX ( tbl3, [SUM_AMT] ) )
RETURN

-----------------------------------------------------------------------------------------------------------------------------------------------
 -- Returning the number of rows whose cumulative sum is less than or equal to 0.2 of total sum
return CountX(filter(tbl4,[CUM_SUM]<=0.2  * [Total_Sum]), Leaderboard[Employee ID])

The output is correct for MTD, YTD and R12 (Rolling 12 Months) in Dax studio.

Whereas its correct for MTD in Power BI but not for YTD and R12. Looks like the expression in return command is not respecting the cross filter applied in the table variables. Its always returning the count of rows whose value is same as MTD.

@fenixen can you please have a look on this one?

[rrr · Accepted solution]

Hey Everyone,

Thank you for taking time to work on the above issue.

After spending some time I realized thtat the date slicer is filtering the rows involved in [SUM_AMT] variable calculation,  although it  is applied when defining the table in summarize function.

This could be because when [SUM_AMT] variable is calculated, old DAX code is not referring to "tbl" variable instead its referrring to Leaderboard which is part of Data Model.

To fix that ,I have modified formula to calculate [SUM_AMT] variable, adding cross filter to it. This solved the issue.

Here is the updated solution for the same.

Thanks

RRR.

Number of Persons in top 20% =

VAR tbl =

SUMMARIZE (

CALCULATETABLE (

FILTER (

ALL ( Leaderboard ),

Leaderboard[EOM] > [Date Start]

&& Leaderboard[EOM] <= [Date End]

&& LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED"

),

CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )

),

'Leaderboard'[Employee ID],

'Leaderboard'[Product Division],

"SUM_AMT",

CALCULATE (

SUM ( 'Leaderboard'[GROSS_MARGIN_AMOUNT] ),

CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )

)

)

VAR tbl2 =

ADDCOLUMNS ( tbl, "rank", RANKX ( tbl, [SUM_AMT],, DESC, SKIP ) )

VAR tbl3 =

CALCULATETABLE (

ADDCOLUMNS (

tbl2,

"Cum_Sum", SUMX ( TOPN ( [rank], tbl2, [sum_amt], DESC ), [Sum_AMt] )

)

)

VAR tbl4 =

CALCULATETABLE ( ADDCOLUMNS ( tbl3, "Total_Sum", SUMX ( tbl3, [SUM_AMT] ) ) )

RETURN

COUNTROWS ( FILTER ( tbl4, [CUm_SUm] <= 0.2 * [Total_Sum] ) )