Forum Discussion
Help with data shaping / data validation in Power BI Desktop
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
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.
5 Replies
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.53. Combine the Criteria in a Single Measure
ValidRows = IF( [PercentageOver20] = 1 && [CategorySumOver50], 1, 0 )Thank you very much Sahir ! Really appreciate your time helping with this.
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.
Hi Ashish
Thank you very much - this seems to have solved my issue! Thanks to you and Sahir for providing solution so quickly 😊
You are welcome.
