Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Price margin calculator

Hello, 

 

I monitor the productmargin in PowerBI based on customer and product niveau. 

I want to know what the marge will be when i change the price of my products. 

I think i can fix this with a parameter, for example a X% price difference. 

But i want to use this price changes only on the selected products or customers (slicer).

Is this possible or how can I build this in PBI. 

 

Or maybe someone have another idea to fix this? 

 

Example data: 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

    You can create two tables(customer no, item no, no relationship to original tables) as source of slicers. Then write a measure to get calculate result from these slicers:

    Changed =
VAR _sItem =
    ALLSELECTED ( Item[Item_No] )
VAR _sCust =
    ALLSELECTED ( Customer[Cust_No] )
VAR _Price =
    SELECTEDVALUE ( Table[Percent] )
VAR _totalSales =
    MAX ( Sample[Price] ) / MAX ( Sample[Margin] )
        * MAX ( Sample[Quantiy] )
RETURN
    IF (
        MAX ( Sample[Cust_No] ) IN _sCust
            && MAX ( Sample[Item_No] ) IN _sItem,
        _totalSales * _price,
        _totalSales
    )

    If above not help, please share a pbix file with sample data and expected result for further test.

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    What parameter you mean? 'Query parameter' or 'what if' parameter? If you mean 'what if' parameter, it is possible. You can write a measure to calculate current row contents with selected value.

    If you mean query parameter, normally slicer/filter can't interact with query parameters. You need to turn on enable 'load option' to load it as a table, then you can use it calculate with selected records.

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      What - if parameter, 

      But i can't fix it

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    You can create two tables(customer no, item no, no relationship to original tables) as source of slicers. Then write a measure to get calculate result from these slicers:

    Changed =
VAR _sItem =
    ALLSELECTED ( Item[Item_No] )
VAR _sCust =
    ALLSELECTED ( Customer[Cust_No] )
VAR _Price =
    SELECTEDVALUE ( Table[Percent] )
VAR _totalSales =
    MAX ( Sample[Price] ) / MAX ( Sample[Margin] )
        * MAX ( Sample[Quantiy] )
RETURN
    IF (
        MAX ( Sample[Cust_No] ) IN _sCust
            && MAX ( Sample[Item_No] ) IN _sItem,
        _totalSales * _price,
        _totalSales
    )

    If above not help, please share a pbix file with sample data and expected result for further test.

    Regards,

    Xiaoxin Sheng