Dynamic ratio

[Varadha123]

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

[Anonymous · Accepted solution]

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

[danextian]

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.

[Akash_Varuna]

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