Forum Discussion
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 |
| Q3 | Furniture | Sofa | 300 | 33% | 14% | Table/Sofa |
| Q3 | Furniture | Desk | 400 | 50% | 3% | Chair/Desk |
| Q4 | Furniture | Table | 70 | Table/Chair | ||
| Q4 | Furniture | Chair | 20 | Chair/Sofa | ||
| Q4 | Furniture | Sofa | 500 | Table/Sofa | ||
| Q4 | Furniture | Desk | 700 | Chair/Desk |
In power bi, I have used Matrix table. Category as furniture , attributes as undiscounted , items are Table,chair,desk in hieriarichy.
I have created a measere : Table value = Divide(calculate(table(value),table[category]="Furniture", table[attributes]="Undiscounted",table [items]="table"],(calculate(table(value),table[category]="Furniture", table[attributes]="Undiscounted", table [items]="chair"
l want to calculate the percentage and the percentage value should be placed in the different rows.. Let's assume the table value should be placed in Chair row. So after created the table value measures I have created one more measure to consolidate
If( category = "Furniture",attributes="Undiscounted" items="Table" switch (true(), items="Chair",[Table value]
Question:
The dax is working fine. I want to dynamically change the value like in the slicers i have previous quarter and current quarter both as q1,q2,q3,q4
I want to create two ratios one is Previous ratio and current ration
If i click the previou slicer as Q3 and current slicer as Q4, the value in the previous ratio and current ratio should calculate the value based on the slicer selection
Q3 table value should divide with the Q3 chair value and the value should be placed in the chair row of the previous ratio likewise Q4 table value should divide with the Q4 Chair value.
Instead of creating separate measures for Q3 and Q4 division measures is there any other way to create one measure that we can dynamically used the calculation for previous and current ratios.
Currently i have created separate division measures for previous and current ratio and consolidate with the another measure
Kindly help
Hi all,thanks for the quick reply, I'll add more.
Hi Varadha123 ,
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
3 Replies
Hi Varadha123 , 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
Hi Varadha123
Can you clarify how you calculated the ratios? For instance, in row 1, the Current Ratio is listed as 350%, calculated as Table/Chair, but dividing Table by Chair (100/200) would give 50%. Additionally, your formula includes attributes and items columns that aren't present in your sample data. Providing a sample data set that accurately reflects your actual use case would help us understand your objective better and eliminate any guesswork regarding the extra columns.
Hi all,thanks for the quick reply, I'll add more.
Hi Varadha123 ,
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
