Column chart using mesures

[alfertab]

Hi everyone, I'm having some troubles with the column chart and I would like to know if someone could help me.

I need to plot a column chart like the following:

My only problem is that all the results that appears in the chart are measures that were calculated from the columns of a table like the following:

Day	Energy Base Load	Energy Int. Load	Energy Peak Load	Energy Base Net	Energy Int Net	Energy Peak Load	Base Hour	Int. Hours	Peak Hours
01/01	1000	1300	1250	1500	1350	100	6	14	4
02/01	1050	1350	1200	1600	1400	150	6	14	4

The measures are calculated as shown below:

Dprom Load base: CALCULATE(SUM(Energy Base Load)) / CALCULATE(SUM(Base Hour)) = 2050/12 = 170.83

Dprom Load Int: CALCULATE(SUM(Energy Int. Load)) / CALCULATE(SUM(Int. Hours)) = 2650/28 = 94.64

Dprom Load peak: CALCULATE(SUM(Energy Peak Load)) / CALCULATE(SUM(Peak Hours)) = 2450/8 =306.25 

Dprom NET base: CALCULATE(SUM(Energy Base Net)) / CALCULATE(SUM(Base Hour)) = 3100/12 = 258.33

Dprom Net Int: CALCULATE(SUM(Energy Int. Net)) / CALCULATE(SUM(Int. Hours)) =2750/28 =98.21

Dprom Net peak: CALCULATE(SUM(Energy Peak Net)) / CALCULATE(SUM(Peak Hours)) = 250/8 = 31.25

[Greg_Deckler · Accepted solution]

@alfertab Create two measures, Measure1 and Measure2 similar to the following:

Measure1 = 
  VAR __AxisValue = MAX('Table'[Axis Column])
  VAR __Result =
    SWITCH( __AxisValue,
      "Base", [Dprom Load Base],
      "Intermediate", [Dpom Net Int],
      [Dprom Net Peak]
    )
RETURN
  __Result


Measure2 = 
  VAR __AxisValue = MAX('Table'[Axis Column])
  VAR __Result =
    SWITCH( __AxisValue,
      "Base", [Dprom NET Base],
      "Intermediate", [Dpom Load Int],
      [Dprom Load Peak]
    )
RETURN
  __Result

[alfertab]

@Greg_Deckler @v-xuxinyi-msft Thanks a lot to both of  you. Personally I used @v-xuxinyi-msft  method (it was a little easier for me to understand) but both worked. 😄

[v-xuxinyi-msft · Accepted solution]

Hi @alfertab 

Thanks to @Greg_Deckler  for the method, here are my additions:

A second table was created.

Measure:

Dprom Load = 
SWITCH(TRUE(),
SELECTEDVALUE('Table (2)'[a]) = "Base", [Dprom Load base],
SELECTEDVALUE('Table (2)'[a]) = "Intermediate", [Dprom Load Int],
SELECTEDVALUE('Table (2)'[a]) = "Peak", [Dprom Load peak])

Dprom Net = 
SWITCH(TRUE(),
SELECTEDVALUE('Table (2)'[a]) = "Base", [Dprom NET base],
SELECTEDVALUE('Table (2)'[a]) = "Intermediate", [Dprom Net Int],
SELECTEDVALUE('Table (2)'[a]) = "Peak", [Dprom Net peak])

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

@alfertab Create two measures, Measure1 and Measure2 similar to the following:

Measure1 = 
  VAR __AxisValue = MAX('Table'[Axis Column])
  VAR __Result =
    SWITCH( __AxisValue,
      "Base", [Dprom Load Base],
      "Intermediate", [Dpom Net Int],
      [Dprom Net Peak]
    )
RETURN
  __Result


Measure2 = 
  VAR __AxisValue = MAX('Table'[Axis Column])
  VAR __Result =
    SWITCH( __AxisValue,
      "Base", [Dprom NET Base],
      "Intermediate", [Dpom Load Int],
      [Dprom Load Peak]
    )
RETURN
  __Result