Optimal values in Power BI

[Syndicate_Admin]

Good morning

I have the following table:

The optimal values are as follows:

The percentages would have to be applied over the "Sales" column.

And they ask me for a bar graph like this:

How can I do this?

Thank you very much and greetings!

[Greg_Deckler]

@Syndicate_Admin Perhaps try this. Place State column from first table in the X-Axis. Create 2 measures for Y-Axis:

Optimo = 
  VAR __State = MAX('Table1'[State])
RETURN
  DIVIDE(
     MAXX(FILTER('Table2',[QMS] = __State),[nueva] +
     MAXX(FILTER('Table2',[QMS] = __State),[recurrente],
     2
  )

Real = 
  VAR __State = MAX('Table1'[State])
  VAR __SalesN = SUMX(FILTER('Table1',[Type]="Nueva"),[Sales])
  VAR __SalesR = SUMX(FILTER('Table1',[Type]="Recurrente"),[Sales])
  VAR __SalesAllN = SUMX(FILTER(ALL('Table1'),[State]=__State &&[Type]="Nueva"),[Sales])
  VAR __SalesAllR = SUMX(FILTER(ALL('Table1'),[State]=__State &&[Type]="Recurrente"),[Sales])
RETURN
  DIVIDE(
     DIVIDE(__SalesN,__SalesAllN,0) + DIVIDE(__SalesR,__SalesAllR,0),
     2
  )

  

[Syndicate_Admin]

@Syndicate_Admin

Hello!

But this is with measurements, but I want it with formulas, so this is not a table in Power BI, it is only the data that I have specified for you to take into account. That is, the percentages are not in a table, I just have to apply them in the formula to calculate the percentage:

That is, for example, all the opportunities that the Area has QMS, the Status is Detected and the Type is New, you have to calculate 5% of the Sales column.

And so with all the other categories.

How can I indicate it in DAX?

Thank you very much in advance and greetings!

[Greg_Deckler]

@Syndicate_Admin OK, yeah, I didn't get that at all from the original post. So how are the Real values calculated versus teh Optimo values? I'm still not sure how the calculation is supposed to work.

[Syndicate_Admin]

@Syndicate_Admin

I explain:

In Power BI I have this table:

And here are the percentages I have to apply (IT's NOT a table or part of the Power BI data model):

The formula would have to be aglo as follows:

Si Type=Nueva && State=Detectado && Area=QMS --> 5% de la columna Sales

Si Type=Nueva && State=Validación && Area=QMS --> 10% de la columna Sales

Si Type=Nueva && State=Evaluación && Area=QMS --> 50% de la columna Sales

And so with the others.

Thank you very much and greetings!