Dynamic Binning and Measures

[bcreed2]

I'm brand new to this software.  I'm not seeking an answer per se, but rather hoping you all can point me in the right direction,  As I understand it, what I'm trying to accomplish within PowerBI isn't neceassrily simple. 

I have an application level data set that contains information used to gauge the performance of several scorecards.  I'm able to obtain the metrics used to assess scorecard performance at the highest level.  In other words, the measures are calculated off of the entire data set.  

What I want to is have the table (shown below) show the various calculations with respect to the selections made in various filters.  For example, If a user selects "Credit Card" the calculations shown will then only be based on the credit card sub-set of my aggregated data set.  I'd also like the binning to automatically reparamaterize itself based on the user filter selections.  If anyone can tell me what I need to understand and what functions in DAX will be needed to do so it'd be greatly apprecaited! 

[v-yulgu-msft · Accepted solution]

Hi @bcreed2,

As it is not supported to dynamically change column fields displayed on table visual depend on slicer selection, there is no direct solution to achieve your requirement.

Here are two workarounds just for your reference which might not meet your requirement exactly.

Unpivot table first. Select both [Product] and [Sub-Product] fields then, click the "Unpivot columns" button.

Then, drag [type] field into slicer and place the other two into table visual.

In that case, table's structure is changed which might affect some mesaures calculation.

Alternatively, you can create an extra table which lists all available slicer selections like below.

Then, use IF condition to wrap your measures, similar to:

Sum total =
IF (
    LASTNONBLANK ( Tb1[Slicer Items2], 1 ) = "Product",
    CALCULATE ( SUM ( Tb2[Values] ), ALLEXCEPT ( Tb2, Tb2[Product] ) ),
    CALCULATE ( SUM ( Tb2[Values] ), ALLEXCEPT ( Tb2, Tb2[Sub-Product] ) )
)

That case, you have to add both two binning groups [Product] and [Sub-Products] into visual.

Best regards,

Yuliana Gu