Forum Discussion
AdministratorOptimal values in Power BI
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!
4 Replies
- Greg_Deckler
Community Champion
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_AdminHello!
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.
