Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Advanced Filtering for Cumulative values

Hi Everyone,    I have a problem with a cumulative measure. Would appreciate if someone could help.   My columns are: "Account ID" - unique for every account and can be found through multiple d...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    I believe that you need to do a condittional formatting measure to make this happen. In this case I have created a calendar table and the following measures:

    cumulative value =
CALCULATE (
    SUM ( 'Table'[Charge] ),
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= MAX ( 'calendar'[Date] ) )
)


Formattingbystatus =
SWITCH (
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'Table'[Status], 'Table'[Date] ),
                'Table'[Date] <= MAX ( 'calendar'[Date] )
            ),
            'Table'[Date], DESC
        ),
        "DDD", 'Table'[Status]
    ),
    "POCI - NPE", "Red",
    "POCI - PE", "Yellow"
)

     

     

    Now if you use the condittional formatting you will get the following result:

    You need to add on the second measure a color for each of the status you have in this example you only share two.

    Check PBIX file attach.

     

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

The calculation that you refer for the 31/12/2018 is correct when you don't have split between the status when you look at the POCI-NPE the value is 397.561 and the -16.748 would be part of the "POCI-PE" .

 

If I understand correctly what you want is that the values present the cumulative in this case 380 but for the status to be presented has part of the higher status in this case the POC-NPE is this correct? 

So in this case we would calculate the 380K and then categorize it has POC-NPE since the total cumulative is lower than the POC-NPE status independently?

 

Can you please elaborate a little bit more.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you Miguel for taking interest.

    Actually what i want is simpler. I just want the Cumulative Charge not to split between statuses.
    I want the cumulative charge to belong to the current status at that date. 


    E.g For 31/05/2019 the cumulative charge will be 771.43. And this 771.43 should be labeled as POCI-NPE in my bar chart (for that date), because POCI-NPE is the status of 31/05/2019.

    A month later, the cumulative charge will change and the status may change as well, so I want the next date's cumulative charge to be labeled with the next date's status.

    But importantly, the cumulative charge should not filter between statuses. It should sum the charges of the whole Account ID irrespective of what statuses it has.

     

    Please let me konw in case of any other questions.

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Hi Anonymous ,

       

      I believe that you need to do a condittional formatting measure to make this happen. In this case I have created a calendar table and the following measures:

      cumulative value =
CALCULATE (
    SUM ( 'Table'[Charge] ),
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= MAX ( 'calendar'[Date] ) )
)


Formattingbystatus =
SWITCH (
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'Table'[Status], 'Table'[Date] ),
                'Table'[Date] <= MAX ( 'calendar'[Date] )
            ),
            'Table'[Date], DESC
        ),
        "DDD", 'Table'[Status]
    ),
    "POCI - NPE", "Red",
    "POCI - PE", "Yellow"
)

       

       

      Now if you use the condittional formatting you will get the following result:

      You need to add on the second measure a color for each of the status you have in this example you only share two.

      Check PBIX file attach.