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

     

v-dineshya's avatar
v-dineshya
Community Support
1 year ago

Hi Anonymous ,

Thanks for reaching out to the Microsoft fabric community forum. 

 

DAX code: 

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 SelectedRows = VALUES('Add Rows:'[View by: Fields])
VAR FirstSelectedRow = MINX(SelectedRows, 'Add Rows:'[View by: Fields])


VAR Total =
SWITCH(
TRUE(),
FirstSelectedRow = "GPH Level1", CALCULATE(EQN, GPH[GPH Level1]),
FirstSelectedRow = "GPH Level2", CALCULATE(EQN, GPH[GPH Level2]),
FirstSelectedRow = "GPH Level3", CALCULATE(EQN, GPH[GPH Level3]),
FirstSelectedRow = "GPH Level4", CALCULATE(EQN, GPH[GPH Level4]),
FirstSelectedRow = "Manufacturer", CALCULATE(EQN, Manufacturer[VENDOR_NAME]),
FirstSelectedRow = "Linebuy", CALCULATE(EQN, Linebuy[LINEBUY_ID]),
FirstSelectedRow = "Business Group", CALCULATE(EQN, Transactions[BUSINESS_GROUP]),
FirstSelectedRow = "PE Brand Type", CALCULATE(EQN, Transactions[PE_BRAND_TYPE]),
FirstSelectedRow = "Region", CALCULATE(EQN, Region[EDMCS_LOB_DESC]),
FirstSelectedRow = "PS Type", CALCULATE(EQN, PS_Type[PS_Type]),
Total
)

RETURN
IF(
HASONEVALUE('Add Rows:'[View by: Fields]),
CALCULATE(EQN),
Total
)

 

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

v-dineshya's avatar
v-dineshya
Community Support
1 year ago

Hi Anonymous ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi Anonymous ,

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards