Forum Discussion

jmeccles's avatar
jmeccles
Helper I
9 years ago
Solved

Dynamic Measure Values Based on Slicer Selection with Options that are Not Connected to the Data

Hi there, Everyone!   So, I have a spreadsheet with sales values as shown below:   [Sales] Account No. Sales Date Sales Year Sales Amount Branch Department Invoice Number ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jmeccles

     

    Hi, Try with this:

     

    1. Create a calculated column in Sales Table:

     

    SalesAmountWAdjustment =
DIVIDE (
    Sales[Sales Amount],
    LOOKUPVALUE (
        'Adjustment Rates'[Nominal Rate],
        'Adjustment Rates'[Year], Sales[Sales Year]
    )
)

     

    2. Create a New Table --Using Enter Data

     

     

    3. Insert a Slicer Visual With Options Field

     

    4. Finally Create a Measure :

     

    Measure =
IF (
    SELECTEDVALUE ( Options[Options] ) = "Adjusted",
    SUM ( Sales[SalesAmountWAdjustment] ),
    SUM ( Sales[Sales Amount] )
)

     

    5. Insert this measure in a Card Visual or another that you desire.

     

     

    Regards

     

    Victor

    Lima - Peru

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

Does anyone have any advice on how to develop a measure that runs from many slicers, with each slicer having more than 10+filters?

I have attached an image where I would like to know the total count based on the customer, location, property type, type of customer, cost category selected in the multiple slicers. Also the option where if nothing is selected on the slicer, then it just counts all cells.

In the solution above, the filter in the slicer is written in the measure. Unfortunately that does not work for me, because in reality I have a load more options than the few in the image, so want to avoid writing these filters in the formula.

 

Hope this makes sense! Any help will be much appreciated.

 

Thanks