Forum Discussion

rlebeau's avatar
rlebeau
New Member
2 years ago

Summarize Table combined with dynamic columns

Hi,

 

I have a formula that looks like this and it works perfectly:

COGS Delta Volume =
 
SUMX(SUMMARIZE(sales,Entity[Legal Entity],TB_Products[Product Category]
)
,([Quantity] - [Qty LY])*[Std Unit Cost LY])
 
However if I want to make the column selection dynamic using a field paramater, it does not work  and gives a slightly different results where totals don't match:
 
COGS Delta Volume =
SUMX(SUMMARIZE('Charts Parameters','Charts Parameters'[Colonnes Fields]
)
,([Quantity] - [Qty LY])*[Std Unit Cost LY])
 
Any idea how I could make the first formula dynmaic based on end user's choice?
 
Thanks in advance for your help!
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    rlebeau , You might have used a switch based on the selected param

     

    selected = maxx(filter('Charts Parameters', 'Charts Parameters'[Axis Slicer Order]= SELECTEDVALUE('Charts Parameters'[Axis Slicer Order])),'Charts Parameters'[Axis Slicer])

     

     

    Then use that in another measure, example
    COGS Delta Volume =
    SUMX(SUMMARIZE('Charts Parameters','Charts Parameters'[Colonnes Fields]
    )
    ,Switch( [selected],
    "Product Category", ([Quantity] - [Qty LY])*[Std Unit Cost LY],
    "Product Name", ([Quantity] - [Qty])*[Std Unit Cost]
    //Add other

    ))

  • rlebeau's avatar
    rlebeau
    New Member
    2 years ago

    Hi,

    First thanks for taking the time to help me. But in your example (if i understand well) you hardcode the product category and product name. I don't know which column the end user is going to select, neither how many.