Forum Discussion

Mooma's avatar
Mooma
Frequent Visitor
3 years ago
Solved

DAX formula needed to extrapolate missing values

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.

DAX
  • DataNinja777's avatar
    DataNinja777
    3 years ago

    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?  

     

     

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    3 years ago

    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?  

     

     

    • Mooma's avatar
      Mooma
      Frequent Visitor
      3 years ago

      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

      • DataNinja777's avatar
        DataNinja777
        Super User
        3 years ago

        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