Create a variable chart that calculates ratios and plots the chart

[Ruz]

Hi,

I am trying to create a variable chart where it will show the ratio of Column "A" and Column "B". Except i need A and B to be variable selected from 15 columns, is that possible?

i.e. I have weekly values of 15 different products and I want to create a chart where I would select my numerator and denominator using slicer and plot the ratio based on time.

Thank you

[Anonymous · Accepted solution]

Hi @Ruz ,

Here is my sample data:

And I build two table to create two slicers for numerator and denominator:

Use this DAX to create a new measure:

Measure = 
VAR Numerator_SUM = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Column] = SELECTEDVALUE('numerator'[numerator])
    )
)
VAR Denominator_SUM = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Column] = SELECTEDVALUE('denominator'[denominator])
    )
)
RETURN
DIVIDE(Numerator_SUM, Denominator_SUM)

Put this measure into the visual, and the final output is below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ruz]

Hi Dino @Anonymous 

I am trying to explore bringing this 1 step further, what I mean is that in this case I want to create same type of variable chart but I only select the numerator and formula would generate chart with all denominator options.

i.e. I select numerator A from slicer and formula calculates and plots A/B, A/C, A/D.

Appreciate your expertise and help.

Thank you!

[Ruz]

Hi Dino,

This works perfectly as what I needed, thank you!

[Daniel29195]

Hello @Ruz 

if you could please share a visual representation of the data and the desired output.

best regards,