Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Clustered column chart and line from the same column

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 YearCalendar MonthValue
Budget2018July10000
Turnover2018July9500
Budget2018August11000
Turnover 2018August10500
Budget2018September12000
Turnover 2018September11500
Budget2018October13000
Turnover 2018October12500
Budget2018November14000
Turnover 2018November13500
Budget2018December15000
Turnover 2018December14500
Budget2019January16000
Turnover 2019January15500
Budget2019February17000
Turnover 2019February16500
Budget2019March18000
Turnover 2019March17500
Budget2019April19000
Turnover 2019April18500
Budget2019May20000
Turnover 2019May19500
Budget2019June21000
Turnover 2019June20500

 

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

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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