Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All
I have following data set :-
BRAND......SEGMENT..........STAFF...........SALES
IBM..............ELECTRONIC...DAVID..........100
GOOGLE....INTERNET.........STEVEN.......200
APPLE..... ...COMPUTER.....PAUL............300
In order to present the report , i need to create 3 Table :-
Table 1 Sales By Brand
Table 2 Sales By Segment
Table 3 Sales By Staff
it is possible Just to create only 1 table , and allow user to select view by Brand or Segment or Staff ?
Paul
Solved! Go to Solution.
Hi @admin11 ,
Here are the steps you can follow:
Create calculated table(summrizeTable1).:
1. Create calculated table
summrizeTable1 = SUMMARIZE('SALES','SALES'[BRAND_C],"sum",SUM('SALES'[sales]))
2. Create calculated column.
rank = RANKX('summrizeTable1','summrizeTable1'[sum],,DESC)
3. Result.
Create calculated table(summrizeTable2).
1. Create calculated table
summrizetable2 =
SUMMARIZE('SEGMENT','SEGMENT'[SEGMENT_C],"sum",SUM('SALES'[sales]))
2. Create calculated column.
rank = RANKX('summrizetable2','summrizetable2'[sum],,ASC)
3. Result.
Connect the relationship between the two tables
Create measure.
_Select_measure =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND_C", MAX('summrizeTable1'[BRAND_C]),
_select="SEGMENT_C", MAX('summrizetable2'[SEGMENT_C]))
_amount_select =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND_C", CALCULATE(SUM('summrizeTable1'[sum]),FILTER(ALL('summrizeTable1'),'summrizeTable1'[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
_select="SEGMENT_C",CALCULATE(SUM('summrizetable2'[sum]),FILTER(ALL('summrizetable2'),'summrizetable2'[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))))
Put [rank] and measure[_Select_measure], [_amount_select] of summrizeTable2 into it.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @admin11 ,
Here are the steps you can follow:
Create calculated table(summrizeTable1).:
1. Create calculated table
summrizeTable1 = SUMMARIZE('SALES','SALES'[BRAND_C],"sum",SUM('SALES'[sales]))
2. Create calculated column.
rank = RANKX('summrizeTable1','summrizeTable1'[sum],,DESC)
3. Result.
Create calculated table(summrizeTable2).
1. Create calculated table
summrizetable2 =
SUMMARIZE('SEGMENT','SEGMENT'[SEGMENT_C],"sum",SUM('SALES'[sales]))
2. Create calculated column.
rank = RANKX('summrizetable2','summrizetable2'[sum],,ASC)
3. Result.
Connect the relationship between the two tables
Create measure.
_Select_measure =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND_C", MAX('summrizeTable1'[BRAND_C]),
_select="SEGMENT_C", MAX('summrizetable2'[SEGMENT_C]))
_amount_select =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND_C", CALCULATE(SUM('summrizeTable1'[sum]),FILTER(ALL('summrizeTable1'),'summrizeTable1'[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
_select="SEGMENT_C",CALCULATE(SUM('summrizetable2'[sum]),FILTER(ALL('summrizetable2'),'summrizetable2'[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))))
Put [rank] and measure[_Select_measure], [_amount_select] of summrizeTable2 into it.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @admin11 ,
Here are the steps you can follow:
1. In Power query, Add Column – Index Column – From 1.
2. Use Enter data to create a table.
3. Create measure.
Select_measure =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND", MAX('Table'[BRAND]),
_select="SEGMENT", MAX('Table'[SEGMENT]),
_select="STAFF", MAX('Table'[STAFF]))
amount_select =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND", CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'),'Table'[BRAND]=MAX('Table'[BRAND]))),
_select="SEGMENT", CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'),'Table'[SEGMENT]=MAX('Table'[SEGMENT]))),
_select="STAFF", CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'),'Table'[STAFF]=MAX('Table'[STAFF]))))
4. Result:
By selecting the slicer, the corresponding information is displayed in the table
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Many Thank for your help. it is what i am looking for.
I have create an expression for select measure :-
But i get below :-
Hope you can advise me where go wrong ?
My PBI file :-
https://www.dropbox.com/s/ia8qjdjr7i1xg7o/Flexy%20Dimension%20V003.pbix?dl=0
Paul Yeo
@admin11 , Create one table and then you can measure
= sum(Table[Sales])
And use them in table visual with BRAND, SEGMENT or STAFF. Depending on need you can one or more of these.
example
BRAND, sales measure
If need you can create a physical table
new Table = summarize(Table, Table[BRAND], "Sales", sum(sales[Sales]))
Tahnk you for your sharing, Base on your reply , You mean still need to create 3 Table. it is not possible to have dynamic dimension for Brand , Segment , Staff in one table as measure ?
The reason I ask is because , I try to reduce number of table . Imagine if dynamic dimension is possible, I can just using one Tab , now I need to use 3 Tab
@admin11 , Very much possible with one table. Just drag what you need visual with sales measure
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |