locked
DAX - Tabular Cube - Wrong Totals populated - Calculated Measure - Excel Pivot RRS feed

  • Question

  • Hello All,

    I am trying to get a proper Grand Total values in Excel Pivot by connecting to the Tabular Cube. We can see (in sample data below) the ConveretdYTD at Customer level the values are fine but the Total value is using the same DAX logic to populate the Grand Total value. I have created the SeatYTD, PaidSeatsYTD Calculated measures logic using DAX SUMX and SUMMARIZE functions to get values aggregated at Customer level

    The DAX logic implemented for getting the ConvertedYTD is below:

    ConvertedYTD:=IF(ISBLANK([PaidSeatsYTD]), 0, IF([PaidSeatsYTD] >= ABS([SeatYTD]), ABS([SeatYTD]), [PaidSeatsYTD]))

    Below is the Sample data:

    Row   Labels Jul, 2015 Aug, 2015 Sep, 2015 Oct, 2015 Nov, 2015 Dec, 2015  Total
    Customer 1              
    SeatsYTD     -83 -157 -157 -157 -157
    PaidSeatsYTD     83 162 164 166 166
    ConvertedYTD 0 0 83 157 157 157 157
    Customer 2              
    SeatsYTD       -57 -57 -57 -57
    PaidSeatsYTD     47 47 47 47 47
    ConvertedYTD 0 0   47 47 47 47
    Customer 3              
    SeatsYTD   -50 -64 -64 -64 -64 -64
    PaidSeatsYTD       61 63 63 63
    ConvertedYTD 0 0 0 61 63 63 63
    Total SeatsYTD   -50 -147 -278 -278 -278 -278
    Total   PaidSeatsYTD     130 270 274 276 276
    Total   ConvertedYTD 0 0 130 270 274 276 276

    Trying   to get these Values 0 0 83 265 267 267 267

    Please suggest how can I get the above values.

    Thanks

    Saturday, January 30, 2016 2:09 AM

Answers

  • The measure you wrote works at the aggregation level of each cell of the pivot table.

    Since you want to apply the logic at the customer granularity, you have to iterate customers applying the formula you wrote (remember using CALCULATE for a context transition):

    ConvertedYTD :=
    SUMX (
        Customers,
        CALCULATE (
            IF (
                ISBLANK ( [PaidSeatsYTD] ),
                0,
                IF ( [PaidSeatsYTD] >= ABS ( [SeatYTD] ), ABS ( [SeatYTD] ), [PaidSeatsYTD] )
            )
        )
    )


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Saturday, January 30, 2016 8:22 AM