Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic DAX

Hi,   I have a measure in Power BI that I am trying to make dynamic. I have a matrix with a field parameter called "Add Rows" in the Rows section of the matrix. The user can select which fields...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous , Check this I have updated the Total

    dax
    Measure =
    VAR CurrentGP = ROUND([Ext. GP%], 10)
    VAR PreviousGP = ROUND([Ext. GP% PY], 10)
    VAR Sales = ROUND([Ext. Sales], 0)
    VAR EQN = (CurrentGP - PreviousGP) * Sales
    VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])
    VAR FirstSelectedRow = SELECTCOLUMNS(TOPN(1, VALUES('Add Rows:'[View by: Fields])), "FirstRow", [View by: Fields])
    VAR Total = SUMX(VALUES('Add Rows:'[View by: Fields]), [Ext. GP Impact Rate Archive])
    VAR Calc =
    SWITCH(
    TRUE(),
    ISINSCOPE(GPH[GPH Level1]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level2]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level3]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level4]), CALCULATE(EQN),
    ISINSCOPE(Manufacturer[VENDOR_NAME]), CALCULATE(EQN),
    ISINSCOPE(Linebuy[LINEBUY_ID]), CALCULATE(EQN),
    ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
    ISINSCOPE(Transactions[PE_BRAND_TYPE]), CALCULATE(EQN),
    ISINSCOPE(Region[EDMCS_LOB_DESC]), CALCULATE(EQN),
    ISINSCOPE(Region[EDMCS_REGION_NAME]), CALCULATE(EQN),
    ISINSCOPE(Linebuy[Commodity.Category1]), CALCULATE(EQN),
    ISINSCOPE(PS_Type[PS_Type]), CALCULATE(EQN),
    ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
    ISINSCOPE(Transactions[Commodity Tie Out Product Type]), CALCULATE(EQN),
    Total
    )
    RETURN
    Calc