Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
@Anonymous
1) Make a table "Category" with only the unique Category in Power BI
2) Create relationships from Data to Category and RAW data to Category
3) New measure
CALCULATE(
SUM ('Raw Data'[Budget] ),
'Raw Data'[Year]=2020,
NewCategoryTable[Category]="New Actives"
)/ 1000000
Your biggest issue is you don't have a proper Data Model, you need to split your data in Fact tables and Dimensions then build relationships
Hi @Anonymous
The second filter is incorrect; you need to add the "Category" column to the "Raw Data" table, then use that in the measure, or use the relation between those two tables.
Try to use Related dax code or share a sample of your data here in a table format.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi @VahidDM
Thanks for prompt reply.
I have excel data where below formula is used:
=SUMIFS('Raw Data'!AB:AB,Data!D:D,$C$501,'Raw Data'!AA:AA,$K$501,'Raw Data'!N:N,J502)/1000000
Below is the sample data set:
below table is Data:
Region | DRBU | Driver | Category | Brand Type | Brand | Methodology type | 2018 | Scope Change | 2019 | Scope Change2 | 2020 | Proc inv | Savings | Scope Change3 | 2021 |
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | Dunhill | Analytics | £ - | £ 39,270.00 | £ 39,270.00 | -£ 39,270.00 | £ - | £ 40,700.00 | £ 40,700.00 | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | Dunhill | Other | £ - | £ - | £ - | £ - | £ - | £ - | £ - | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | Dunhill | Qual | £ - | £ 285,840.00 | £ 285,840.00 | -£ 106,490.00 | £ 179,350.00 | £ 279,700.00 | £ 459,050.00 | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | Dunhill | Quant | £ 38,700.68 | £ 95,351.32 | £ 134,052.00 | -£ 134,052.00 | £ - | £ - | £ - | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | KENT | Analytics | £ - | £ 95,010.00 | £ 95,010.00 | -£ 95,010.00 | £ - | £ - | £ - | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | KENT | Other | £ - | £ - | £ - | £ - | £ - | £ - | £ - | ||
CENTRAL | Combustibles Insights | Discretionary | Combustibles | Global Brand | KENT | Qual | £ 206,238.76 | £ 275,969.24 | £ 482,208.00 | -£ 34,202.99 | £ 448,005.01 | -£ 212,287.11 | £ 235,717.90 |
Below Table is Raw Data:
Name | Description | Category | Category - FH | Category level 2 | Category level 3 | Category level 4 | Methodology type | Methodology Group | Year | Budget |
5041 | Lucky Strike Modern AF Link | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | Communication Testing | 2018 | 20,024 |
5545 | Star LE BPT | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | Product Testing | 2018 | 11,394 |
5545 | Star LE BPT | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | Product Testing | 2018 | 10,192 |
6638 | Pall Mall BOOST BPT | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | Product Testing | 2018 | 12,064 |
6638 | Pall Mall BOOST BPT | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | Product Testing | 2018 | 22,467 |
6788 | 2017 4cast KS Circumference | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | NPI/Concept/Mix Testing | 2018 | 39,640 |
6788 | 2017 4cast KS Circumference | Cigarettes | Combustibles | Combustibles | Cigarettes | Combustibles | Quant | NPI/Concept/Mix Testing | 2018 | 61,825 |
6834 | Track - 2018 | MultiCategory / Corporate | Multi-Category | Multi-Category/Corporate | Quant | Segmentation & Tracking | 2018 | 84,084 | ||
6834 | Track - 2018 | MultiCategory / Corporate | Multi-Category | Multi-Category/Corporate | Quant | Segmentation & Tracking | 2018 | 42,183 | ||
7260 | Incidencia | MultiCategory / Corporate | Multi-Category | Multi-Category/Corporate | Quant | Segmentation & Tracking | 2018 | 49,347 |
Category FH in Raw Data and Category in Data have same attributes.
Please suggest
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |