Help with data shaping / data validation in Power BI Desktop

[DC2024]

Hi,

First time posting on here, relatively new user to Power BI and am a little stuck on how to build what I want to build!

I have a data table with the below columns (simplified for the purposes of this example). The 'ID' is the "top-level" record, and for each ID there are sub-ID rows which have category A, B or C. Each of these sub-IDs make up a percentage of the "top-level" ID, all summing to 100%.

The table contains entire history of each ID's sub-ID records over time (via the Record Date column).

ID	Record Date (DD/MM/YYYY)	Sub-ID	Category	Percentage of ID
1000	01/01/2024	100	A	20%
1000	01/01/2024	101	A	10%
1000	01/01/2024	205	A	20%
1000	01/01/2024	103	B	20%
1000	01/01/2024	400	B	30%
1000	01/02/2024	100	A	10%
1000	01/02/2024	101	A	20%
1000	01/02/2024	650	C	40%
1000	01/02/2024	670	C	10%
1000	01/02/2024	690	C	20%
1001	15/12/2023	100	A	50%
1001	15/12/2023	103	B	30%
1001	15/12/2023	150	B	20%
1001	04/01/2024	160	B	50%
1001	04/01/2024	650	C	20%
1001	04/01/2024	660	C	30%

What I'd like is to have a visual that can filter only for rows where, for each ID and record date:

- The 'Percentage of ID' column is >20% for an individual row, AND

- The summed 'Percentage of ID' column for that row's category is >50%.

In the above sample data, this would mean that only the row highlighted in red (ID 1000 / Sub-ID 650 / Record Date 01/02/2024) would pass the above criteria. That's because that row has weight >20% (40%), AND that sub-ID's category (Category C) for ID 1000 for that Record Date sums to 70%.

I've managed to create a calculated column to allow me to filter for any records that pass the first criteria, but am struggling to create a column or measure that will give me both. Everything I have tried so far only calculates at the row-level.

Apologies if this doesn't make sense - let me know if you need any more information!

Thank you

DC

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Column = CALCULATE(SUM(Data[Percentage of ID]),FILTER(data,Data[ID]=EARLIER(Data[ID])&&Data[Record Date (DD/MM/YYYY)]=EARLIER(Data[Record Date (DD/MM/YYYY)])&&Data[Category]=EARLIER(Data[Category])))

Column 2 = if(AND(Data[Percentage of ID]>0.2,Data[Column]>0.5),1,0)

Hope this helps.

[DC2024]

Hi Ashish

Thank you very much - this seems to have solved my issue! Thanks to you and Sahir for providing solution so quickly 😊

[Ashish_Mathur]

You are welcome.

[Sahir_Maharaj]

Hello @DC2024,

Can you please try this:

1. Create a Measure for Row-Level Percentage Criteria (I know you've mentioned you've already done this, but including it for completeness)

PercentageOver20 = IF(MAX('Table'[Percentage of ID]) > 0.2, 1, 0)

2. Create a Measure for Category-Level Summed Percentage Criteria

CategorySumOver50 = 
CALCULATE(
    SUM('Table'[Percentage of ID]),
    ALLEXCEPT('Table', 'Table'[ID], 'Table'[Category], 'Table'[Record Date])
) > 0.5

3. Combine the Criteria in a Single Measure

ValidRows = 
IF(
    [PercentageOver20] = 1 && [CategorySumOver50],
    1,
    0
)

[DC2024]

Thank you very much Sahir ! Really appreciate your time helping with this.