How to make use of one table to replace 3 table ?

[admin11]

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

[v-yangliu-msft · Accepted 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

[v-yangliu-msft]

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

[admin11]

@v-yangliu-msft 

Many Thank for your help. it is what i am looking for.

I have create an expression for select measure  :-

_Select_measure =

var _select=SELECTEDVALUE(SliceTable[Slice])

return

SWITCH(

TRUE(),

_select="BRAND", MAX('SALES'[BRAND_C]),

_select="SEGMENT", MAX('SEGMENT'[SEGMENT_C]),

_select="STAFF", MAX('SALES'[G_TYPE]))

I also create amount expression :-

_amount_select =

var _select=SELECTEDVALUE(SliceTable[Slice])

return

SWITCH(

TRUE(),

_select="BRAND_C", CALCULATE(SUM('SALES'[sales]),FILTER(ALL('SALES'),'SALES'[BRAND_C]=MAX('SALES'[BRAND_C]))))

I expect to get below result :-

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

[amitchandak]

@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]))

[admin11]

@amitchandak 

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

[amitchandak]

@admin11 , Very much possible with one table. Just drag what you need visual with sales measure

[admin11]

@amitchandak 

Are you refer to using Visual as i high light red box ?