cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hariprasadpargu
Frequent Visitor

Creating an Aggregated table and filter top and bottom 10

Hi All,

 

I am new to Power BI, so I am sorry if I am asking some basic questions:

I have a table with the following columns:

Key1    Key2   Key3   Dimension1   Dimension2   Dimension3

I want to create an aggregated table based on Key1 and Key2 and then find Top/Bottom 10 based on SUM(Dimension1) column.
Is there a way I can do this in one Summarize statement?

3 REPLIES 3
Anonymous
Not applicable

TopAndBottom =
var __table =
	ADDCOLUMNS(
		SUMMARIZE(
			T,
			T[Key1],
			T[Key2]
		),
		"SumOfDim1", CALCULATE( SUM( T[Dimension1] ) )
	)
var __top =
	TOPN(  
		10,
		__table,
		[SumOfDim1],
		DESC
	)
var __bottom =
	TOPN(  
		10,
		__table,
		[SumOfDim1],
		ASC
	)
var __union =
	DISTINCT(
		Union(
			__top,
			__bottom
		)
	)
return
	__union

Best

Darek

Hi Darek,

Thanks for the solution!

I tried your approach for my problem but I am ending up with issues related to % calculation in DAX Quer-2. For some reason, it is bringing up all the SEND_TO customers. Also, I coudn't find a way to combine the logic from Query-1 and Query-2.


Please note:
I manually checked the data and values in __final_summarized_table1 are accurate.

 

 

DAX Query-1:

__Intermediate_table = 
Var __summarized_table1 =
    ADDCOLUMNS(summarize(EDI_TRANS_BY_CUST, EDI_TRANS_BY_CUST[SEND_TO]), "Avg Transmission", calculate(SUM(EDI_TRANS_BY_CUST[COUNT])/DISTINCTCOUNT(EDI_TRANS_BY_CUST[YR_WK_NBR])))

Var __summarized_table2 = 
    ADDCOLUMNS(SUMMARIZE(EDI_SMRY_LAST_WEEK, EDI_SMRY_LAST_WEEK[SEND_TO]), "Sum of Lst Wk Transmission", CALCULATE(SUM(EDI_SMRY_LAST_WEEK[COUNT])))

Var __merge_summarized_table =
    UNION(
        SELECTCOLUMNS(__summarized_table1, "SEND_TO", [SEND_TO], "Avg Transmission", [Avg Transmission], "Sum of Lst Wk Transmission", 0),
        SELECTCOLUMNS(__summarized_table2, "SEND_TO", [SEND_TO], "Avg Transmission", 0, "Sum of Lst Wk Transmission", [Sum of Lst Wk Transmission])
        )

var __final_summarized_table1 = 
    GROUPBY(__merge_summarized_table, [SEND_TO], "Avg Transmission", SUMX(CURRENTGROUP(), [Avg Transmission]), "Sum of Lst Wk Transmission", SUMX(CURRENTGROUP(), [Sum of Lst Wk Transmission]))

return __final_summarized_table1


DAX Query-2:

__Top_30 = 
    TOPN(30, __Intermediate_table, ((sum('__Intermediate_table'[Sum of Lst Wk Transmission]) - sum('__Intermediate_table'[Avg Transmission])) / sum('__Intermediate_table'[Avg Transmission])) * 100, DESC)

 

 
Anonymous
Not applicable

Hi.

 

First of all, you should not torture people with unformatted DAX. Please go to www.daxformatter.com, paste your DAX in there, format it and then copy the formatted version like this:

__Intermediate_table =
VAR __summarized_table1 =
    ADDCOLUMNS (
        SUMMARIZE (
            EDI_TRANS_BY_CUST,
            EDI_TRANS_BY_CUST[SEND_TO]
        ),
        "Avg Transmission", CALCULATE (
            SUM ( EDI_TRANS_BY_CUST[COUNT] )
                / DISTINCTCOUNT ( EDI_TRANS_BY_CUST[YR_WK_NBR] )
        )
    )
VAR __summarized_table2 =
    ADDCOLUMNS (
        SUMMARIZE (
            EDI_SMRY_LAST_WEEK,
            EDI_SMRY_LAST_WEEK[SEND_TO]
        ),
        "Sum of Lst Wk Transmission", CALCULATE (
            SUM ( EDI_SMRY_LAST_WEEK[COUNT] )
        )
    )
VAR __merge_summarized_table =
    UNION (
        SELECTCOLUMNS (
            __summarized_table1,
            "SEND_TO", [SEND_TO],
            "Avg Transmission", [Avg Transmission],
            "Sum of Lst Wk Transmission", 0
        ),
        SELECTCOLUMNS (
            __summarized_table2,
            "SEND_TO", [SEND_TO],
            "Avg Transmission", 0,
            "Sum of Lst Wk Transmission", [Sum of Lst Wk Transmission]
        )
    )
VAR __final_summarized_table1 =
    GROUPBY (
        __merge_summarized_table,
        [SEND_TO],
        "Avg Transmission", SUMX (
            CURRENTGROUP (),
            [Avg Transmission]
        ),
        "Sum of Lst Wk Transmission", SUMX (
            CURRENTGROUP (),
            [Sum of Lst Wk Transmission]
        )
    )
RETURN
    __final_summarized_table1

Secondly, please do not name tables with __ in front of their names. Double underscores are intended only for variable names and nothing else. If you want to mark anything like a table or a measure or a column as hidden, then you can use a single underscore (but this only marks it so, you have to then hide them manually).

 

Thirdly, I'd suggest you use meaningful names for all your objects. Avoid abbreviations and anything that is hardly readable. If you cannot easily pronounce the name of something, it's time to think about a better name. This EDI_TRANS_BY_CUST[YR_WK_NBR] is a very, very, very bad name. Both names are extremely BAD, the table's and the column's. If I were your manager, honestly, I'd make you redundant in no time. And I'm not joking 🙂

 

Fourthly, I'd get rid of GROUPBY and replace it with something like the SUMMARIZE/ADDCOLUMNS pattern. Many reasons why. I've never in my career had to use GROUPBY... Why did you do it? Any particular reason?

 

Fifthly, what are you trying to achieve with this:

__Top_30 =
TOPN (
    30,
    __Intermediate_table,
    (
        (
            SUM ( '__Intermediate_table'[Sum of Lst Wk Transmission] )
                - SUM ( '__Intermediate_table'[Avg Transmission] )
        )
            / SUM ( '__Intermediate_table'[Avg Transmission] )
    ) * 100, DESC
)

I know you're trying to get the top 30 rows but what's the value by which you want to order? First of all, TOPN is an ITERATOR. Please read about what iterators do, what is context transition, what are aggregation functions, what filtering context is and what row context is. I'm 100% sure you have no foggiest idea about these and how they interact. You want to write good DAX and understand what is being computed? Then read a good book on DAX. Or take a course. This is a good piece of advice.

 

Lastly, I suspect you might want something like this:

__Top_30 =
TOPN (
    30,
    __Intermediate_table,
    
    var __sum = __Intermediate_table[Sum of Lst Wk Transmission] )
    var __avg = __Intermediate_table[Avg Transmission]
    return
    	divide( __sum - __avg, __avg )
)

 

Once again, I'll tell you this. I've learned DAX for about 3-4 years now. I've read many books and have taken 3 courses. The books and courses I'm talking about were written/created by The Italians (try to find out who I'm talking about). DAX is a very, very, very complex language and if you're not totally comfortable with all the intricacies of it (about some of which I've written before), you'll be in trouble more often than not. To the point where you'll be calculating something and you'll have no foggiest idea what the code is doing. This will be HIGHLY FRUSTRATING. At the same time, DAX---if you know it---can be really fun and it's VERY, VERY, VERY POWERFUL.

 

You've been warned.

 

Best

Darek

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.