Field Parameters in Matrix Sales Share Calculation

[robranieri]

Hello PBI Community,

First and foremost, the underlying data and the PBI example (let me know if the links don't work):
Excel Data 

PBI Report 

There are two datasets in the Excel: Sales data has ProductID, RetailerKey, TimeID and Sales. The prod hierarchy (ProductH) has ProductID and other categorical information like Category, Subcategory, Brand, & Item. 

I have a very specific issue that is arising from how parameters work inside of a matrix. The use-case is that users need to be able to define their rows and their columns via field parameters and see their sales share within those slices. In the example data, I've made 2 identical field parameters in order to solve for this ask so that users can pick what they want to see. In the screenshot below, I need to adjust the "Denominator" measure so that based on whatever is selected in the Columns it maintains that specific column's total. Therefore the share values should represent the share within that specific option selected.

It should look more like this (when the column is by Retailer):

The denominator adjusts dynamically above so that the share values are now all correct. I can hard-code this for the Retailer, but need to make it reflect and adjust based on whatever is selected (and it can be more than one selection) in the Columns field parameter. 

What I need is the DAX code for the denominator that adjusts dynamically in order to calculate the correct share values.

Thanks All,

Rob

[v-hashadapu]

Hi @robranieri , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[robranieri]

No, the issue has not been resolved

[v-hashadapu]

Hi @robranieri , Thanks for the update. But can you be more specific on the results and share any other relevent details.

Thank you.

[v-hashadapu]

Hi @robranieri , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @robranieri , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[danextian]

Field parameters don’t dynamically adjust how filters are applied in your measure. No matter which field is selected from the parameter, the formula will still apply ALLSELECTED to the Sales table as a whole.

CALCULATE ( [Sales], ALLSELECTED ( Sales ) )

You will need to write a conditional measure to detect which parameter value is being selected.

Denominator =
SWITCH (
    SELECTEDVALUE ( ColumnParameter[ColumnParameter Order] ),
    1, CALCULATE ( [Sales], ALLSELECTED ( Sales[RetailerKey] ) ),
    2, CALCULATE ( [Sales], ALLSELECTED ( ProductH[Category] ) ),
    3, CALCULATE ( [Sales], ALLSELECTED ( ProductH[Subcategory] ) ),
    4, CALCULATE ( [Sales], ALLSELECTED ( ProductH[Brand] ) ),
    5, CALCULATE ( [Sales], ALLSELECTED ( ProductH[Item] ) )
)

Power BI does not allow directly referencing the Parameter value thus the use of Parameter Order column instead. 

[robranieri]

This did not work:

It also has to be ready to calculate for when mutliple options are selected, not just a single selected value from the Column parameters. 

I think we're on the right track though!

[v-hashadapu]

Hi @robranieri , Thank you for reaching out to the Microsoft Community Forum.

Please try below and share your thoughts.

DenominatorDynamic = CALCULATE([Sales], REMOVEFILTERS('ProductH'[Subcategory]),KEEPFILTERS(VALUES(ColumnParameter[ColumnParameter) 

then just use:

SalesShare = DIVIDE([Sales], [DenominatorDynamic])