Forum Discussion

showy's avatar
showy
Helper II
1 year ago
Solved

Dynamic Measure with Fieldparameters

Hello   I have some sample data here.   I have a D_Item with the article 12345, which has 4 values (Value 1 - Value 4)   In the fact table I have a few quantities for the article 12345.   The...
Need Help
Show and Tell
Tips and Tricks
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    showy could you please use beow updated measure to get correct Grand Total.

    _Measure(Updated)2 = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])

Var Result = 
SUMX(D_Item, 
    Var Quantity = CALCULATE(SUM(F_Sales[Quantity]))
    Var SelectedPrice = SWITCH (SelectedColumn,
                        "Price1", D_Item[Price1],
                        "Price2", D_Item[Price2],
                        "Price3", D_Item[Price3],
                        "Price4", D_Item[Price4],
                        BLANK())
    Var Result = Quantity * SelectedPrice //Final Result1
    Var Result2 = IF(ISBLANK(SelectedPrice), Quantity, Quantity * SelectedPrice) //Final Result2
    RETURN
    Result2 //Based on business needs chose any one result
    )
    
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

showy's avatar
showy
Helper II
1 year ago

Hi ajaybabuinturi this works very good and it the behaviour i wanted.

I tried it now with multiple Items but now i am facing a strange topic. I added 2 more items and Entry in F_Sales.
The switch between Price1 - Price 4 works and the values are correct but the total sum dont reacts to the selection. 

Do u also have a solution for that?

 I also updated the dropbox link with the current data

I think i have to use SUMX but i dont get it right....



ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

showy could you please use beow updated measure to get correct Grand Total.

_Measure(Updated)2 = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])

Var Result = 
SUMX(D_Item, 
    Var Quantity = CALCULATE(SUM(F_Sales[Quantity]))
    Var SelectedPrice = SWITCH (SelectedColumn,
                        "Price1", D_Item[Price1],
                        "Price2", D_Item[Price2],
                        "Price3", D_Item[Price3],
                        "Price4", D_Item[Price4],
                        BLANK())
    Var Result = Quantity * SelectedPrice //Final Result1
    Var Result2 = IF(ISBLANK(SelectedPrice), Quantity, Quantity * SelectedPrice) //Final Result2
    RETURN
    Result2 //Based on business needs chose any one result
    )
    
RETURN
Result

 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

 

  • showy's avatar
    showy
    Helper II
    1 year ago

    Thanks that works very well 🙂