Forum Discussion

robranieri's avatar
robranieri
Regular Visitor
1 year ago
Solved

Field Parameters in Matrix Sales Share Calculation

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 da...
Need Help
Show and Tell
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi robranieri , To provide a tailored solution for your specific issue, we need to review your data and details thoroughly, which requires access to your .pbix file. If sharing the .pbix file is not possible due to confidentiality, could you provide a sample file with fake data that closely matches your scenario and data? This will help us better understand and address the issue.
    Thank you.

danextian's avatar
danextian
Super User
1 year ago

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's avatar
    robranieri
    Regular Visitor
    1 year ago

    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's avatar
      v-hashadapu
      Community Support
      1 year ago

      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])