Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors