PBI measure ratio - non existent data

Question

On my PowerBI dashboard I do have the measure that presents SUM(REVENUE)/SUM(QUANTITY) ratio. Problem is that data into DB about revenue and quantity does NOT come in the same time. So it could happened that data of quantities come into DB for first three months and data about revenue for two months only. My measure then will present sum of two months revenue divided by sum of quantities from all three months and this does not represent real ratio!

So I need some trick to tell PBI (measure) to SUM only quantities of months where there exists corresponding revenue!

Answer 1

You can try with this below measure-

ratio = 
CALCULATE(
    sum(your_table_name[revenue]),
    FILTER(
        all(your_table_name),
        ISNUMBER(your_table_name[quantity])
    )
)
/ sum(your_table_name[quantity])

Here is sample data and output-

---

Another option you create a custom column with this below-

actual_revenue = 
    (your_table_name[quantity] * your_table_name[revenue])
    /your_table_name[quantity]

And create this below measure-

ratio = SUM(your_table_name[actual_revenue])/SUM(your_table_name[quantity])

The output will be same as below-