Forum Discussion

admin11's avatar
admin11
Icon for Memorable Member rankMemorable Member
4 years ago
Solved

How to make my Table 1 display 11 Brands ?

Hi All

 

Anonymous have help me on below post :-

https://community.powerbi.com/t5/Desktop/How-to-make-use-of-one-table-to-replace-3-table/m-p/2260406#M821766

I try to follow his instruction and create Table 2.

 

Table 1 Report is i just place below field name to striaght table  :-

BRAND_c

sales

 

Below Table 1 is correct as it display 11 Brands.

 

Below Table 2 Report 

 

_Select_measure =
var _select=SELECTEDVALUE(SliceTable[Slice])
return
SWITCH(
TRUE(),
_select="BRAND_C", MAX('SALES'[BRAND_C]),
_select="SEGMENT", MAX('SEGMENT'[SEGMENT_C]),
_select="STAFF", MAX('SALES'[G_TYPE]))
 
_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]))))
 
Table 1 is wrong because it only display 1 brand. it should display 11 brand.

 

My question is how to make both table similar display full list of brand ?

 

Some thing wrong with my expression on _amount_select

 

Below is my PBI sample file :-

https://www.dropbox.com/s/3h1tot6rt2wiwua/Flexy%20Dimension%20V005.pbix?dl=0

 

Paul 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    admin11 , Both are measures used in the table. Seem like you want to change Axis/Group by. That is possible using bookmark or Unpivot data. Not using measure

     

    Dynamically change chart axis in Power BI
    bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
    https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
    https://www.youtube.com/watch?v=6jeSIRpjv0M
    https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

     

     

     

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, admin11 ;

    Please try it.

    _amount_select = 
SWITCH(
    SELECTEDVALUE(SliceTable[Slice]),
"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]), 
    CALCULATE(SUM('summrizeTable1'[sum]),FILTER(ALL('summrizeTable1'),'summrizeTable1'[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
    SUM('SALES'[sales])),
"SEGMENT_C",IF(ISINSCOPE(summrizetable2[rank]),
    CALCULATE(SUM('summrizetable2'[sum]),FILTER(ALL('summrizetable2'),'summrizetable2'[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))),
    SUM('SALES'[sales])))

    The final output is shown below:

    Or:

    _amount_select2 = 
SWITCH(
SELECTEDVALUE(SliceTable[Slice]),
"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]), 
  CALCULATE(SUM('SALES'[sales]),FILTER(ALLSELECTED('SALES'),[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
  SUM('SALES'[sales])),
"SEGMENT_C",IF(ISINSCOPE(summrizetable2[rank]),
   CALCULATE(SUM('SALES'[sales]),FILTER(ALLSELECTED('SEGMENT'),[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))),
   SUM('SALES'[sales])))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    admin11 , There is nothing in lese where brand <> BRAND_C

     

    I added else for testing

     

    _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]))),
    SUM('SALES'[sales])
    )

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， admin11 ；

    I tested it and which return the correct result.

    1.when i select BRAND_C.

    2.when I select the SEGMENT_C.

    If you want to show 11 brands ,you must have rank column (who more), and it will ok.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • v-yalanwu-msft's avatar
        v-yalanwu-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, admin11 ;

        Please try it.

        _amount_select = 
SWITCH(
    SELECTEDVALUE(SliceTable[Slice]),
"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]), 
    CALCULATE(SUM('summrizeTable1'[sum]),FILTER(ALL('summrizeTable1'),'summrizeTable1'[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
    SUM('SALES'[sales])),
"SEGMENT_C",IF(ISINSCOPE(summrizetable2[rank]),
    CALCULATE(SUM('summrizetable2'[sum]),FILTER(ALL('summrizetable2'),'summrizetable2'[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))),
    SUM('SALES'[sales])))

        The final output is shown below:

        Or:

        _amount_select2 = 
SWITCH(
SELECTEDVALUE(SliceTable[Slice]),
"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]), 
  CALCULATE(SUM('SALES'[sales]),FILTER(ALLSELECTED('SALES'),[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))),
  SUM('SALES'[sales])),
"SEGMENT_C",IF(ISINSCOPE(summrizetable2[rank]),
   CALCULATE(SUM('SALES'[sales]),FILTER(ALLSELECTED('SEGMENT'),[SEGMENT_C]=MAX('summrizetable2'[SEGMENT_C]))),
   SUM('SALES'[sales])))

        The final output is shown below:

        Best Regards,
        Community Support Team_ Yalan Wu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • admin11's avatar
      admin11
      Icon for Memorable Member rankMemorable Member
      4 years ago

      v-yalanwu-msft 

      I just notice that the total amount wrong. it is possible to make Table 1 and Table 2 both same value ?