Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I have a set of data given below. Iwant top 5 assoicate name by spend as well by division. For example the division (Top5 would be in Column) and associate name would be in Rows in Matrix Visualization.
Data:-
Associate | Division | Spend |
A1 | Road | 234 |
A2 | Services | 343 |
A1 | Home | 223 |
A4 | Services | 555 |
A5 | Group | 4234 |
A6 | Medical | 234 |
A7 | Music | 344 |
A6 | Home | 2344 |
A9 | Vehicle | 434 |
A6 | Hospital | 434 |
A11 | Class | 2 |
A12 | Veg | 23434 |
A13 | Cream | 234 |
Expeted Output
Associate | Group | Home | Hospital | Services | Veg | Grand Total |
A12 | 23434 | 23434 | ||||
A5 | 4234 | 4234 | ||||
A6 | 2344 | 434 | 3012 | |||
A4 | 555 | 555 | ||||
A1 | 223 | 457 |
I am able to fetch top 5 associate name by spend but not by division (Need top 5 division as well as in column and overall total)
Kindly help.
Solved! Go to Solution.
Hi @Anonymous
I've attached a file with a solution.
Hi Everyone,
Adding one more situation in the given scenario. (I am using SQL server direct query source hence unable to add new tables in model)
I want to filter category first and then in the filter category I want fetch top 5 associate name and top 5 divison in that particulat category.
Below is the Raw data
Category | Associate | Division | Spend |
Business | A1 | Road | 234 |
Asset | A2 | Services | 343 |
Electriical | A1 | Home | 223 |
Labour | A4 | Services | 555 |
Business | A5 | Group | 4234 |
Asset | A6 | Medical | 234 |
Electriical | A7 | Music | 344 |
Labour | A6 | Home | 2344 |
Business | A9 | Vehicle | 434 |
Asset | A6 | Hospital | 434 |
Electriical | A11 | Class | 2 |
Labour | A12 | Veg | 23434 |
Business | A13 | Cream | 234 |
Asset | A6 | Home | 2344 |
Electriical | A9 | Vehicle | 434 |
Labour | A6 | Hospital | 434 |
Asset | A11 | Class | 2 |
Result would be say I filtered Category using page level Filter as Asset. Therfore under asser Top5 supplier and Top 5 spend would be like below snapshot. Mine motive is to segereate the Associate name by category and under cateory which are the Top5 associate and their spend with Top5 division(In column in powerBi)
Kindly help I am struggling on this from last couple of weeks. I am new in PowerBi therefore need support from you.
Regards
Uphar Tandon
Hi @Anonymous
Try this Measure
SpendTop5=
VAR __topN = 5
VAR __associate =
RANKX (
ALL ( 'Table'[Associate] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Associate] ) ),
,
DESC,
DENSE
) <= __topN
VAR __division =
RANKX (
ALL ( 'Table'[Division] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Division] ) ),
,
DESC,
DENSE
) <= __topN
RETURN
IF( __division && __associate, [spend] )
Hi Mariusz,
Are these two seperate measures? I tried to create only one measure and not getting the desired Output i.e
SpendTop5=
VAR __topN = 5
VAR __associate =
RANKX (
ALL ( 'Table'[Associate] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Associate] ) ),
,
DESC,
DENSE
) <= __topN
VAR __division =
RANKX (
ALL ( 'Table'[Division] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Division] ) ),
,
DESC,
DENSE
) <= __topN
RETURN
IF( __division && __associate, [spend] )
See I am getting below output from your given DAX
Apologies, As I am noob in DAX queries.
Kindly Help.
Hi @Anonymous
Can you share your DAX expression?
Hi @Anonymous
I've attached a file with a solution.
Hi @Mariusz
Thank you so much for your suggestion. Here, I replaced All with All selected and now able to get the results.
The only change I have done here is that I am only fetching divsion from DAX Top 5 associate names are filtering out through powerbi Top N Filter option and for Category I am filtering through page level filter.
But its works and giving the correct output.
Below Is the query which I performed.
VAR __division =
RANKX (
SelectedALL ( 'Table'[Division] ),
CALCULATE(
[spend],
ALLEXCEPT( 'Table', 'Table'[Division] ) ),
,
DESC,
DENSE
) <= __topN
RETURN
IF( __division && __associate, [spend] )
Really appreciate your efforts.
Regards
Uphar Tandon
Hi @Anonymous ,
This one is tricky and you can do it in the following way.
Your Master Table is named Top5
1. Create a Table for TOP 5 Associates.
TOP 5 Associates = TOPN ( 5, ADDCOLUMNS ( SUMMARIZE ( Top5, Top5[Associate] ), "SUMASS", CALCULATE ( SUM ( Top5[Spend] ), ALLEXCEPT ( TOP5, Top5[Associate] ) ) ), [SUMASS], DESC )
2. Create a Table for TOP 5 Divisions
Table 5 Divisions = TOPN ( 5, ADDCOLUMNS ( SUMMARIZE ( Top5, Top5[Division] ), "SUMDIV", CALCULATE ( SUM ( Top5[Spend] ), ALLEXCEPT ( TOP5, Top5[Division] ) ) ), [SUMDIV], DESC )
3. Create Calculated Column in TopN Table i.e Master Table
Hi Harsh,
I am using Direct Query and fetching data from SQl server hence I really dont want to add addtional column and table as my data size is huge.
Really appreciate for your suggesstion.
Regards
Uphar Tandon
Hi @Anonymous
Sure, you can use this Measure
Rank =
RANKX (
ALL ( 'Table'[Associate] ),
CALCULATE ( SUM ( 'Table'[Spend] ) ),
,
DESC,
DENSE
)
Hi Mariuz,
I can see top 5 associate name by spend but it is not fulfilling my requirments as I also need Top 5 Division it means in Rows I need associate name (Top 5 ) and in Column I need (Top 5 ) Division name and finally total.
Please help.
This appears to be what you are going for?
Hi,
Thank you for your suggestion.
Need top 5 also Division in column and overall total in the end. In your visualisation I can see 6 division. And in my final data I have lots of other division.
Regards
UPhar
This one was tricky! (to do this inside a matrix visual). Here is one way to do it. The matrix matches your original visual (excep the total). I broke it up into pieces/variables to make it easier to follow/adjust. I ran out of time, but the you can it one step further to also get the total showing correct (i.e., sumx(values(SpendTable[Division]), [Top 5 Div and Assoc]), and use that as the measure instead). Not confirmed but that should work.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |