I am creating a dashboard in Power BI to visualize some metrics. The data is gathered from an excel sheet, and everything is sorted by stages. The project names repeats for each metric, and repeats over multiple stages until they reach the end.
The issue is, i need to make a visual for some Learning / Experiment ratio and learning velocity, but struggle to divide the data I have to get the ratio. (Number of validated learning divided by Number of experiments with customers)
I have tried multiple things, like measurement, measured column, creating reference tables, but I struggle to do what I need.
My current data setup: data excel
LE Ratio = CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value] ); FILTER ( 'New Pilot Metrics'; 'New Pilot Metrics'[Innovation Metric] = "Number of validated learnings" ) ) / CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value] ); FILTER ( ALLEXCEPT ( 'New Pilot Metrics'; 'New Pilot Metrics'[Innovation Stage] ); 'New Pilot Metrics'[Innovation Metric] = "Number of experiments with customer" ) ) This is the closest I got, I expected it to calculate for each stage or at least each project name, but it did the total of everything.
I could do this in the excel file itself (this is a dummy), but the real deal is in use at the moment and would require me to involve multiple persons to do the correction.
2 Answers
It looks like your LE Ratio measure could be:
LE Ratio = VAR TotalLearnings = CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value] ), FILTER ( 'New Pilot Metrics', 'New Pilot Metrics'[Innovation Metric] = "Number of validated learnings" ) ) + 0 VAR TotalExperiments = CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value] ), FILTER ( 'New Pilot Metrics', 'New Pilot Metrics'[Innovation Metric] = "Number of experiments with customer" ) ) + 0 RETURN DIVIDE ( TotalLearnings, TotalExperiments, BLANK() ) Which can return something like:
The answer from Olly works like charm!
I was also able to solve it my way after taking a break with a simpler code.
L/E Ratio = DIVIDE ( CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value]); 'New Pilot Metrics'[Innovation Metric] = "Number of validated learnings" ); CALCULATE ( SUM ( 'New Pilot Metrics'[Numeric metric value]); 'New Pilot Metrics'[Innovation Metric] = "Number of experiments with customer" ) ) 
