Forum Discussion

Silvie's avatar
Silvie
Icon for Helper I rankHelper I
2 years ago
Solved

MAT calculation wrong

Hi,    I posted this on Saturday as a reply on an existing topic, but didn't get any answer yet. That's why I'm creating a new message.  I'm new at calculating rather complex formulas. I need to c...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Your data model looks reasonable. Personally I would merge the Periode Type data into the Calendar table (like you did in the Periode Dimensies OLD.

     

    You must use &&  , not  ||  

     

    You must use the date from the calendar table, not from the fact table

     

    % Nutriscore MAT = 
var d = max('Calendar'[Date])
var a = filter(ALLSELECTED(Nutriscore),Nutriscore[Date]<=d && Nutriscore[Date]>EDATE(d,-12))
return if (countrows(a)=12,divide(sumx(a,Nutriscore[OOP Nutriscore A-B]),sumx(a,Nutriscore[OOP Nutriscore]),0))
Silvie's avatar
Silvie
Icon for Helper I rankHelper I
2 years ago

I tried to visualize the results in a line chart or table, but I'm not getting any results. 

When I try to select the organization (Org) or organization type (Org Type) it also doesn't work. I should be able to select between GROUP, VERTICAL or OU as org type. 

I've put my pbix file on my Onedrive: A016 Ecoscore - A017 Nutriscore.pbix

Maybe someone can find out what I'm doing wrong? Or should I start all over? 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Your data model looks reasonable. Personally I would merge the Periode Type data into the Calendar table (like you did in the Periode Dimensies OLD.

 

You must use &&  , not  ||  

 

You must use the date from the calendar table, not from the fact table

 

% Nutriscore MAT = 
var d = max('Calendar'[Date])
var a = filter(ALLSELECTED(Nutriscore),Nutriscore[Date]<=d && Nutriscore[Date]>EDATE(d,-12))
return if (countrows(a)=12,divide(sumx(a,Nutriscore[OOP Nutriscore A-B]),sumx(a,Nutriscore[OOP Nutriscore]),0))