Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Irregular Quarter Comparison

Hi,   I have data for 4 quarters. The idea is to compare one particular column's value with the another.   Quarter             Value 2020q1             100 2020q2             200 2020q3       ...
Need Help
Show and Tell
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    I'm not clear about the red mark:

    When Q1 and Q2 selected, why aren't the places marked in red returning null values?

     

    Try measure as:

    CQ = 
var quar=
MAXX(
    ALLSELECTED('Table'[Quarter]),
    'Table'[Quarter])
return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Quarter]=quar && 'Table'[Company]=MAX('Table'[Company]) && 'Table'[Deptt]=MAX('Table'[Deptt])
    ),
    'Table'[Value]
)
    PQ = 
var _tab=
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Index],
    'Table'[Quarter],
    'Table'[Value],
    'Table'[Company],
    'Table'[Deptt]
)
return
MAXX(
    FILTER(
        _tab,
        [Index] < MAX('Table'[Index]) && 'Table'[Company]=MAX('Table'[Company]) && 'Table'[Deptt]=MAX('Table'[Deptt])
    ),
    [Value]
)

     Here is the output:

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

     

     

v-xulin-mstf's avatar
v-xulin-mstf
Community Support
5 years ago

Hi Anonymous

 

You need add a index column:

Try measure as:

PQ = 
var _tab=
SUMMARIZE(
    ALLSELECTED('Table'),
    'Table'[Index],
    'Table'[Quarter],
    'Table'[Value]
)
return
MAXX(
    FILTER(
        _tab,
        [Index] < MAX('Table'[Index])
    ),
    [Value]
)

Create column as:

CQ = 
CALCULATE(
    MAX('Table'[Value]),
    ALLSELECTED('Table'[Quarter])
)

Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    Looks like your solution is correct although i am having trouble implementing it.

    It is giving me same value for each PQ when implemented.

     

    PQ_GO_1 =
    var _tab=
    SUMMARIZE(
    ALLSELECTED('Data files'),
    'Data files'[Index],
    'Data files'[Quarter],
    'Data files'[company],
    'Data files'[dept],
    'Data files'[salary]
    )
    return
    MAXX(
    FILTER(
    _tab,
    [Index] < MAX('Data files'[Index])
    ),
    [salary]
    )
     
     
    I want to summarize it using company & deptt and get the total salary of each company & deptt wise. However, it is showing same value.
    Any solution for this?
    • v-xulin-mstf's avatar
      v-xulin-mstf
      Community Support
      5 years ago

      Hi Anonymous,

       

      Could you provide your sample data and expected output?

       

      Best Regards,

      Link

       

      Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Company     Deptt.   Quarter    Salary

        ABC              Sales      2020Q1          10000

        ABC              Sales      2020Q2           20000

        ABC              Marketing  2020Q1       20000

        ABC               Marketing  2020q3       50000

        DEF              Accounts     2020q1        400000

        DEF              Accounts     2020q2         400000

         

         

        Final results should be when Q3 & q1 is selected

        ABC      Sales              null      10000

        ABC      Marketing     50000   20000

        DEF       Accounts       400000   null