Forum Discussion

mb0307's avatar
mb0307
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

% slider to show % range

Hi all,   In my Sales Table,  Sales% is calculated at row level agaisnt the Target column.   First visual Table (not Matrix),  shows Product, Sales, Target and % like this:   Requirement ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi mb0307 

    Not very clear about the result you want. 

    You said you have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables. Do you want to filter through the product column in the PRODUCT table ? 

    If so, try this.

    Measure_edit = 
var _product=SELECTEDVALUE('Product'[Product])
var _filtertable=FILTER(ALL('Sales Table'),'Sales Table'[Product]=_product&&[Column_%]>0)
VAR _minrange=CALCULATE(MIN([Column_%]),_filtertable)
var _maxrange=CALCULATE(MAX([Column_%]),_filtertable)
return FORMAT(_minrange,"Percent")&"-"&FORMAT(_maxrange,"Percent")

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
5 years ago

Hi mb0307 

Try this.

-

Create a column:

Column_% = DIVIDE('Sales Table'[Sales],'Sales Table'[Target ])

Then, create a measure:

Measure_%_Range =
VAR _filtertable =
    FILTER ( ALLEXCEPT ( 'Sales Table', 'Sales Table'[Product] ), [Column_%] > 0 )
VAR _minrange =
    CALCULATE ( MIN ( [Column_%] ), _filtertable )
VAR _maxrange =
    CALCULATE ( MAX ( [Column_%] ), _filtertable )
RETURN
    FORMAT ( _minrange, "Percent" ) & "-"
        & FORMAT ( _maxrange, "Percent" )

Result:

Please check the sample file I attached below.

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • mb0307's avatar
    mb0307
    Icon for Responsive Resident rankResponsive Resident
    5 years ago

    v-xiaotang 

     

    Exactly what I need but one question which I forgot to add in the post:

     

    I have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables, like this::

     

    Can the measure be tweaked for PRODUCT dim table and all other columns (sales, target and %) are in Sales table.  Highlighted in red below:

    'Product'[Product]

     

     

     

    Measure_%_Range =
    VAR _filtertable =
    FILTER ( ALLEXCEPT ( 'Sales Table', 'Sales Table'[Product] ), [Column_%] > 0 )
    VAR _minrange =
    CALCULATE ( MIN ( [Column_%] ), _filtertable )
    VAR _maxrange =
    CALCULATE ( MAX ( [Column_%] ), _filtertable )
    RETURN
    FORMAT ( _minrange, "Percent" ) & "-"
    & FORMAT ( _maxrange, "Percent" )

     

    Thanks

    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi mb0307 

      Not very clear about the result you want. 

      You said you have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables. Do you want to filter through the product column in the PRODUCT table ? 

      If so, try this.

      Measure_edit = 
var _product=SELECTEDVALUE('Product'[Product])
var _filtertable=FILTER(ALL('Sales Table'),'Sales Table'[Product]=_product&&[Column_%]>0)
VAR _minrange=CALCULATE(MIN([Column_%]),_filtertable)
var _maxrange=CALCULATE(MAX([Column_%]),_filtertable)
return FORMAT(_minrange,"Percent")&"-"&FORMAT(_maxrange,"Percent")

      Best Regards,

      Community Support Team _ Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

      • mb0307's avatar
        mb0307
        Icon for Responsive Resident rankResponsive Resident
        5 years ago

        Worked.  Thank you so much for your response. 👍