Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Use Measure as a slicer without Unpivot Data

Hi everyone,   I have to re-create the sample view where % Expected Payments and % Expected Accounts are division calculations. Slicer 1 : Month Slicer 2: Division metrics - % Expected Payments a...
Need Help
Show and Tell
  • deepak91g's avatar
    deepak91g
    6 years ago

    Hi Anonymous ,


    1. Remove all table transformations from Power Query steps.

    2. Create two Measures %ExpectedAccounts  and %ExpectedAccounts

    3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.

    https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/

     

    %ExpectedAccounts =
    DIVIDE (
        SUM ( 'Table1 (2)'[Accounts] ),
        SUM ( 'Table1 (2)'[Estimated Accounts] )
    )
    %ExpectedPayments =
    DIVIDE (
        SUM ( 'Table1 (2)'[Payments] ),
        SUM ( 'Table1 (2)'[Estimated Payments] )
    )
    AmountMeasure =
    VAR SlicerValue =
        SELECTEDVALUE ( 'Attribute Mapper'[Value] )
    RETURN
        SWITCH ( SlicerValue, 2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


    Thanks

    Deepak Gupta
    BI Consultant @Prodata

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak ,

 

Thanks for the revert. But, I am not sure if this post is helpful in my scenerio where I want to put together the divison measure in one slicer for the graph. If I use this approach, I can have the legends of the division metrics in the graph with the metrics as slicer but won't be able to use the month as legend.

 

Please correct me if I am wrong?

 

deepak91g's avatar
deepak91g
Resolver II
6 years ago

Hi Anonymous ,


1. Remove all table transformations from Power Query steps.

2. Create two Measures %ExpectedAccounts  and %ExpectedAccounts

3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.

https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/

 

%ExpectedAccounts =
DIVIDE (
    SUM ( 'Table1 (2)'[Accounts] ),
    SUM ( 'Table1 (2)'[Estimated Accounts] )
)
%ExpectedPayments =
DIVIDE (
    SUM ( 'Table1 (2)'[Payments] ),
    SUM ( 'Table1 (2)'[Estimated Payments] )
)
AmountMeasure =
VAR SlicerValue =
    SELECTEDVALUE ( 'Attribute Mapper'[Value] )
RETURN
    SWITCH ( SlicerValue, 2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


Thanks

Deepak Gupta
BI Consultant @Prodata

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    deepak91g , It works. Thank you so much ! 🙂