Clustered column chart and line from the same column

[Anonymous]

Dear All, 

We have a requirement where column values and line values are from same column. I will explain with an example. 

Here is the Data set we have now

Type	Calendar Year	Calendar Month	Value
Budget	2018	July	10000
Turnover	2018	July	9500
Budget	2018	August	11000
Turnover	2018	August	10500
Budget	2018	September	12000
Turnover	2018	September	11500
Budget	2018	October	13000
Turnover	2018	October	12500
Budget	2018	November	14000
Turnover	2018	November	13500
Budget	2018	December	15000
Turnover	2018	December	14500
Budget	2019	January	16000
Turnover	2019	January	15500
Budget	2019	February	17000
Turnover	2019	February	16500
Budget	2019	March	18000
Turnover	2019	March	17500
Budget	2019	April	19000
Turnover	2019	April	18500
Budget	2019	May	20000
Turnover	2019	May	19500
Budget	2019	June	21000
Turnover	2019	June	20500

Now, the requirement is that turnover should be columns and budget should be in lines.

Here below is the plot I could make now. It has cumulative values of Budget and Turnover (named as Umsatz in plot) in columns and Budget once again as Line.

Now, I dont want Budget in columns. If I filter Budget out, then the line will also disappear.  

Could you let me know if there is a way to put budget in line and turnover in columns?

Best Regards,

Sai Srinvas

[Anonymous · Accepted solution]

Hi @Anonymous ,

In fact, these requirements can't achieved based on default column aggregation functions. If you want to achieve some particular calculation which different than current aggregates, you need to write measure formulas with all, allexcept functions to ignore specific filter effect on current calculations and use it on line fields.

Measure =
VAR currdate =
    DATEVALUE (
        MAX ( 'Table'[Calendar Month] ) & "/1/"
            & MAX ( 'Table'[Calendar Year] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            DATEVALUE ( 'Table'[Calendar Month] & "/1/" & 'Table'[Calendar Year] ) <= currdate
        )
    )

Understanding DAX Auto-Exist

Row Context and Filter Context in DAX

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

Regards,

Xiaoxin Sheng