Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DC2024
Frequent Visitor

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).

 

IDRecord Date (DD/MM/YYYY)Sub-IDCategoryPercentage of ID
100001/01/2024100A20%
100001/01/2024101A10%
100001/01/2024205A20%
100001/01/2024103B20%
100001/01/2024400B30%
100001/02/2024100A10%
100001/02/2024101A20%
100001/02/2024650C40%
100001/02/2024670C10%
100001/02/2024690C20%
100115/12/2023100A50%
100115/12/2023103B30%
100115/12/2023150B20%
100104/01/2024160B50%
100104/01/2024650C20%
100104/01/2024660C30%

 

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1709344109445.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1709344109445.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Sahir_Maharaj
Super User
Super User

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
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.