Forum Discussion

ChrisPBI's avatar
ChrisPBI
Advocate III
10 years ago

Different Calculations

Hello,

 

I have some calculations I try to recreate within DAX, but without success yet.

 

My table strucutre is:

 

 

 

 

 

 

 

 

 

 

 

 

This table is the starting point:

 

 

 

 

 

 

The entire proplem is that I don't consider 2016, it's the only year, which is out of scope.

So each calculation needs some kind of restriction to "all years, but 2016"

 

The first calculation is:
Average Total GGE = Total GGE (2008 - 2015) / Total Amount of Products (2008 - 2015)

 

My approach is:

Average Total GGE = 'GGE Amounts'[GGE in kg CO2e] / IF(RELATED('Models'[Release Date]) <> 2016; DISTINCTCOUNT('Models'[Product]))

 

But this only bringt the total GGE by considering all years.

 

Changing the condition (<>2016) does not affect anything.

 

I'm very thankful for any help.

 

Thanks and Regards,

Chris

Need Help

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    10 years ago

    ChrisPBI

     

    According to your description, do you mean there is no data of 2016 in 'GGE Amounts'[GGE in kg CO2e], but table 'Models' contains data of 2016? And I can't see any relationship between table 'GGE Amounts' and 'Models' from the table structure you provided above.

     

    If this is the case, the following formula should meet your needs:

    Average Total GGE =
CALCULATE (
    'GGE Amounts'[GGE in kg CO2e] / DISTINCTCOUNT ( 'Models'[Product] ),
    'Models'[Release Year] <> 2016
)

    If this is not the case, could you post some sample data in your case? Even better you can upload a pbix file.

    • ChrisPBI's avatar
      ChrisPBI
      Advocate III
      10 years ago

      Tryed it, does not work.

       

      Power BI did not recognize my tables.

       

      Regards,

      Chris

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        10 years ago

        ChrisPBI

         

        So which table is not recognized? Could you screenshot the relationship between the tables?:smileyhappy:

         

        Regards