Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic ratio

Hi All, Table: Quarter Category Items Value Previous Ratio Current Ratio Calculation Q3 Furniture Table 100 50% 350% Table/Chair Q3 Furniture Chair 200 67% 4% Chair/Sofa ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi all,thanks for the quick reply, I'll add more.

    Hi Anonymous ,

    Correct me if I'm misunderstanding.

    Assuming this is your data table

    Use the following DAX expression to create a column

    Column = Left([Calculation],FIND("/",[Calculation]) - 1)
    Column2 = RIGHT([Calculation],LEN([Calculation]) - FIND("/",[Calculation]))

    Use the following DAX expression to create two tables for slicers

    current quarter = VALUES('Table'[Quarter])
    previous quarter = VALUES('Table'[Quarter])

    Use the following DAX expression to create  measures

    Previous Ratio = 
VAR _quarter = SELECTEDVALUE('previous quarter'[Quarter])
VAR _numerator = SUMX(FILTER(ALL('Table'),[Quarter] = _quarter && [Items] = SELECTEDVALUE('Table'[Column])),[Value])
VAR _denominator = SUMX(FILTER(ALL('Table'),[Quarter] = _quarter && [Items] = SELECTEDVALUE('Table'[Column2])),[Value])
RETURN DIVIDE(_numerator,_denominator)
    Current Ratio = 
VAR _quarter = SELECTEDVALUE('current quarter'[Quarter])
VAR _numerator = SUMX(FILTER(ALL('Table'),[Quarter] = _quarter && [Items] = SELECTEDVALUE('Table'[Column])),[Value])
VAR _denominator = SUMX(FILTER(ALL('Table'),[Quarter] = _quarter && [Items] = SELECTEDVALUE('Table'[Column2])),[Value])
RETURN DIVIDE(_numerator,_denominator)

    Final output

     

    Best Regards

     

Akash_Varuna's avatar
Akash_Varuna
Icon for Super User rankSuper User
1 year ago

Hi Anonymous  , Please try these steps

  • Create a Dynamic Measure: Use SELECTEDVALUE to fetch slicer selections for Previous Quarter and Current Quarter and calculate the respective Table and Chair values using CALCULATE for each quarter.
  • Calculate Ratios Dynamically: Create a measure to divide Table values by Chair values based on slicer selections and use SWITCH to display results for the selected quarter dynamically.
  • Ensure Row Placement: Add logic in the measure to display the calculated ratio only in the Chair row using a condition like IF(Table[Items] = "Chair", [Dynamic Ratio], BLANK()).
  • Integrate with Matrix Table: Add the measure to your matrix visual, ensuring slicers for Previous Quarter and Current Quarter are applied for dynamic ratio calculations.
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance