Dynamic comparison of a population of rule breaks, between various datasets

[Anonymous]

I am looking to perform a dynamic comparison of a population of rule breaks, between various datasets (days).

The real data contains 30days with about 3m rows per day, however the concept would remain the same. Each day we received a breaks report which provides details of (1) the rule number that is breaking and (2) the deals under each rule that are causing the break.

If all the deals under a specific rule has been fixed, then the rule will disappear from the report (as will the deal code), which means the rule was solved. Equally the number of deal breaks under a rule (or in total) may change from (e.g.) 18 to 15 – however this does not necessarily mean 3 deals were fixed, as you may have 11 deals that were fixed, but 8 new breaks – resulting in a net movement of 3.

Refer to the below data as an example.

Within Power BI I want the user to be able to do the following:

1. Select the most recent period they are interested in – in the example Day5 (already have a filter in place for this)
2. Select the prior period they want to use for comparison – Day 1 in the example (already have a filter in place for this)

Note that they should be able to compare any combination of two days against each other.

Based on the above selection I am already showing the total number of rule breaks for each period, as well as the total number of deal breaks. What I would like to add is:

• How many rules were solved between the two periods
• How many new rules are breaking
• How many deals were fixed
• How many new deal breaks have occurred and are outstanding

For the two deal metric I will also overlay a filter for the Rule Nr, so the user can view changes in totality or for a specific Rule.

I am not quite sure how to construct the measures to arrive at my final calcs to present in a visual. Any advice?

[Anonymous]

@Anonymous,

Based on your current sample data, please create the following columns in your table(In my sample table, the table is named Table2).

rankcol = RANKX(FILTER(Table2,Table2[RULE NR]=EARLIER(Table2[RULE NR])),Table2[DEAL CODE],,ASC)

Count = CALCULATE(COUNT(Table2[RULE NR]),ALLEXCEPT(Table2,Table2[RULE NR],Table2[rankcol]))

Day = RIGHT(Table2[PERIOD],LEN(Table2[PERIOD])-3)

maxday of all rules = MAX(Table2[Day])

maxday per rule = CALCULATE(MAX(Table2[Day]),ALLEXCEPT(Table2,Table2[RULE NR]))

check new break = IF(Table2[Day]=Table2[maxday per rule]&&Table2[Count]=1&&Table2[maxday per rule]=Table2[maxday of all rules],1,0)

newbreak = CALCULATE(COUNT(Table2[RULE NR]),ALLEXCEPT(Table2,Table2[RULE NR]),Table2[check new break]=1)

allcount = CALCULATE(COUNT(Table2[RULE NR]),ALLEXCEPT(Table2,Table2[RULE NR]))

notsolved = CALCULATE(COUNT(Table2[RULE NR]),ALLEXCEPT(Table2,Table2[RULE NR]),Table2[Count]=2)

solved = Table2[allcount]-Table2[newbreak]-Table2[notsolved]

If you still have questions, please share sample data that we can copied and pasted following the guide in this blog.


Regards,
Lydia