Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
@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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |