Forum Discussion

mimaneeisha's avatar
mimaneeisha
Frequent Visitor
3 years ago

Wrong Column Total in Matrix

Dear Power BI Community,

 

I am writing this query to request your support on the below issue faced while trying to get visuals for my data.

 

Background: - My Datasheet contains Date column, Segment, Sub-segment, and EBIT details. The First column “Year” pertains to data related to how many periods.

For example – If it says “3M 2021” – It refers to the value showing in EBIT as related to the first quarter of 2021.

When it is “6M 2021” – It reflects values for (First Quarter + Second Quarter)

When it is “9M 2021” – It reflects values for (First Quarter + Second Quarter + Third Quarter)

When it is “12M 2021” – It reflects values for the entire year.

Likewise, data is given for 6 Years. But “3M 2022” – will only contain data related to the first quarter of 2022.

 

Requirement – Since the given data is YTD level, I wanted to capture quarter-wise details in Power BI visuals through the measure.

So, If I click on Quarter 2 of 2021 – I want data should flow only from the Second quarter instead of additions. For this, I calculated a measure that provides correct data at the cell level in the matrix.

 

Problem Faced – When I try to see the column level total – It provides some negative figure or incorrect figure instead of providing additions of what is reflected in the visual. So, that is where I am facing challenges for the past multiple days, and to seek a solution I am writing problems to the larger group.

 

Measure Used – This is the measure I am using right now for deriving EBIT numbers:

EBIT =

VAR EBITQ1 = CALCULATE(sum(Segment_all_years[EBIT]),FILTER('My Date Table','My Date Table'[Quarter Number]=1))

VAR SumofEBIT = Sum(Segment_all_years[EBIT])

VAR SumofEBITfor234qtr = CALCULATE(sum(Segment_all_years[EBIT]),DATEADD('My Date Table'[Date],0,QUARTER))-CALCULATE(SUM(Segment_all_years[EBIT]),DATEADD('My Date Table'[Date],-1,QUARTER))

Return

    IF(QUARTER(LASTDATE('My Date Table'[Date]))=1,(EBITQ1),SumofEBITfor234qtr)

 

This is the result I am obtaining from Power BI

 

 

This is the Result I want to obtain

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mimaneeisha ,

     

    Please try:

    EBIT =
VAR EBITQ1 =
    CALCULATE (
        SUM ( Segment_all_years[EBIT] ),
        FILTER ( 'My Date Table', 'My Date Table'[Quarter Number] = 1 )
    )
VAR SumofEBIT =
    SUM ( Segment_all_years[EBIT] )
VAR SumofEBITfor234qtr =
    CALCULATE (
        SUM ( Segment_all_years[EBIT] ),
        DATEADD ( 'My Date Table'[Date], 0, QUARTER )
    )
        - CALCULATE (
            SUM ( Segment_all_years[EBIT] ),
            DATEADD ( 'My Date Table'[Date], -1, QUARTER )
        )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Segment] ),
        IF (
            QUARTER ( LASTDATE ( 'My Date Table'[Date] ) ) = 1,
            ( EBITQ1 ),
            SumofEBITfor234qtr
        ),
        CALCULATE ( [MeasureName], ALLSELECTED ( 'My Date Table'[Quarter] ) )    // Calculate the total using a different formula
    )

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • mimaneeisha's avatar
      mimaneeisha
      Frequent Visitor
      3 years ago

      Hi Anonymous ,

       

      I had put this measure as mentioned above 

      EBIT 2 =
      VAR EBITQ1 = CALCULATE(sum(Segment_all_years[EBIT]),filter('My Date Table','My Date Table'[Quarter Number]=1)
      )
      VAR SumofEBIT =
          sum (Segment_all_years[EBIT])
      VAR SumofEBITfor234qtr =
          CALCULATE(SUM(Segment_all_years[EBIT]),
          DATEADD('My Date Table'[Date],0,QUARTER)
          )
              -CALCULATE(SUM(Segment_all_years[EBIT]),
              DATEADD('My Date Table'[Date],-1,QUARTER)
              )
      return
          IF(
              HASONEVALUE(Segment_all_years[Segment]),
              if(QUARTER(LASTDATE('My Date Table'[Date])) = 1,
              (EBITQ1),
              SumofEBITfor234qtr
          ),
          CALCULATE(SUM(Segment_all_years[EBIT]),ALLSELECTED('My Date Table'[Quarter])))
       
      This resulted in the below table - which has an Incorrect row as well as column total, while numbers at cell level are correct.

       

  • mimaneeisha's avatar
    mimaneeisha
    Frequent Visitor
    3 years ago

    Hi Anonymous ,

     

    Thank you for responding. I had this doubt what should I put in "Measure Name"?

     CALCULATE ( [MeasureName], ALLSELECTED ( 'My Date Table'[Quarter] ) )    // Calculate the total using a different formula