Calculate/Create a total value an show this value as an average line

[Tobias106]

Hello everbody,

I have a line chart with 12 month in the x-axis.

My question: How can I calculate the red Total A/B with 0,194 and show this Total as linear average line with the 0,194 for all month?

Month	1	2	3	...	12	Total
A	5	4	6		4	19
B	30	26	30		12	98
A/B	0,17	0,15	0,20		0,33	0,194

Thank you 🙂

Tobias

[PaulDBrown]

Try:

Linear average =
VAR _A =
    CALCULATE ( [Values Displayed], ALL ( Table[month] ), Table[A/B] = "A" )
VAR _B =
    CALCULATE ( [Values Displayed], ALL ( Table[month] ), Table[A/B] = "B" )
RETURN
    IF ( ISBLANK ( [Values Displayed] ), BLANK (), DIVIDE ( _A, _B ) )

[Tobias106]

@PaulDBrown Thank you again, it works. You are my life-saver 🙂

[PaulDBrown · Accepted solution]

Let:
Month field = Table  [month]
A/B field = Table [A/B]

[Values Displayed] = SUM(Table[Value])

Try:

Linear average =
VAR _A =
    CALCULATE ( [Values Displayed], ALL ( Table[month] ), Table[A/B] = "A" )
VAR _B =
    CALCULATE ( [Values Displayed], ALL ( Table[month] ), Table[A/B] = "B" )
RETURN
    DIVIDE ( _A, _B )

[Tobias106]

@PaulDBrown Thank you very much - it works perfectly 🙂

But one thing appears.

In my Datasource Excel I have blank lines. Now I get the correct result 0,194, but not only for the month 1-12. I also have a additional blank row. How can I exclude it per code? 

Month	A/B
Blank	0,194
1	0,194
2	0,194
3	0,194
...	0,194
12	0,194

Thank you again and have a nice day!

Tobias