topic Re: DAX formula needed to extrapolate missing values in DAX Commands and Tips

DAX formula needed to extrapolate missing values

Mooma — Fri, 01 Sep 2023 15:10:03 GMT

Dataset:

Table:

Table2:

What I have so far:

Sales % = CALCULATE(DIVIDE(SUM([Sales]),CALCULATE(SUM([Sales]),ALLSELECTED('Table'))))

Profit = SUM([Profit Amount])

Only profit of Id = aaa is always available.

I need to extrapolate "Profit" for Ids bbb, ccc, ddd. Sales % depends how many Ids selected.

It should calculate Total profit = 1000 / 60% = 1666.67

Profit of bbb = Total profit * 20% and so on.

Expected result if all Ids selected:

Expected result if only Ids aaa and bbb selected:

I appreciate your help.

Re: DAX formula needed to extrapolate missing values

DataNinja777 — Sat, 02 Sep 2023 14:03:09 GMT

Hi Mooma

I am not sure if I am addressing your question properly, as you already have extrapolated profits in the visualization, but does the following measure answer your question?

Re: DAX formula needed to extrapolate missing values

Mooma — Mon, 04 Sep 2023 08:14:33 GMT

Hi DataNinja777

"Extrapolated profit" result is what I'm looking for, unfortunately I cannot replicate it.

I see you added table "ID". What are the relationships?

Thanks

Re: DAX formula needed to extrapolate missing values

DataNinja777 — Mon, 04 Sep 2023 09:39:58 GMT

Hi @Mooma,

The relationship between the ID dimension table and two fact tables with numbers

are as shown below, as both fact tables contained the numbers, I thought it is easier to separate out the ID dimension table for clarification purposes and create relationships with the two fact tables.

I attach the pbix file for this task:

https://1drv.ms/u/s!AlqFfzVTqicpnW54pZuvMrAu9qKv?e=ANmLbD

Re: DAX formula needed to extrapolate missing values

Mooma — Mon, 04 Sep 2023 10:16:38 GMT

That works. Thank you