Forum Discussion
Calculation Context in Power BI desktop while creating new Measure
Hi Guru's,
I am starting my dev journey with Power BI and need some help on calcualtion context for my Power BI Desktop report.
My Question is:
"Numeric Value in one of the attribute from data table is repeating every row but that is static value for combination of few other columns, how can we ignore row level calculation and still use that numeric value for other grouped calculation in power BI"
Clarifying with an Example data set:
Column A, Column B and Column C are (Dimesion) in my data
I have Column D and Column E as 2 measures in the same data set
I am bringing Column F, Column G and Column H from another table by joining on the basis of Column A/B/C which are between both the tables.
Now if I pull out A,B,C,D,E in the report all good D and E are summarize and grouped by A/B/C values all OK
but if I want to use numeric value (F/G/H) from other table the calculation goes each row but what I want is F/G/H should be same value while grouping on the basis of A/B/C
A B C D E F G H
Prodcut1 City1 Postal Code1 10 12 1 1 1
Prodcut1 City1 Postal Code1 22 13 1 1 1
Prodcut1 City1 Postal Code1 34 45 1 1 1
Prodcut2 City2 Postal Code2 07 30 2 2 2
Prodcut2 City2 Postal Code2 15 29 2 2 2
Prodcut2 City2 Postal Code2 14 31 2 2 2
If I summarize:
A B C D E
Prodcut1 City1 Postal Code1 66 70
Prodcut2 City2 Postal Code2 36 90
But if I want to create another calculated column I want to use summarize value of D and E but single value of F/G/H as 1 for product 1 and 2 for product 2 in my calculation, but what is happening is the derived column is calcualting row level and the formula works at each row.
so lets say if I want to create a calcualted column by multiplying D with F then it should be 66*1 for Product 1 and 36*2 for Product 2 in my calculation.
I understand this is calcualtion context and use to do such things in Business Obejcts by for all and for each function but not able to perform the same in Power BI Desktop
any pointer on this is helpful
Thanks!
9 Replies
- Sahir_Maharaj
Super User
Hello saurabhg,
Can you please try this:
1. Summarized Value of D
Total D = SUM(YourTable[D])2. Unique Value of F
Unique F = MIN(YourTable[F])3. Combined Measure
D times Unique F = [Total D] * [Unique F]When you use the D times Unique F measure in a visual along with A, B, C, it should display the product of the sum of D and the unique value of F for each group defined by A, B, C. Should you require any further assistance, please do not hesitate to reach out to me.
Hi Sahir,
Thanks for the reply but still the calculation doesnt works as
Total D = SUM(YourTable[D])
gives me total sum of values under column D which is - 102, even after using it along with other columns A,B and C
Hi saurabhg ,
Could you please provide some raw data in your table1(which include the field A, B and C) and table2(which include F,G and H) (exclude sensitive data) with Text format, the formula of E&F and your expected result? By the way, is there any relationship between table1 and table2? If yes, please provide the relationship info(cardinality, direction etc.). It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Table 1 :
PRODUCT_ID SUB_PRODUCT_ID CATEGORY SALES_DATE SALES_PERSON_ID TOTAL_PRODUCT_COUNT TOTAL_SALES PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 101 50 3 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 102 100 7 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 103 23 6 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 104 24 5 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 105 52 2 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 106 43 4 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 107 63 3 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 108 19 3 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 109 20 7 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 101 45 6 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 102 29 5 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 103 30 2 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 104 31 4 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 105 42 3 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 106 57 3 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 107 85 7 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 108 62 6 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 109 34 5 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 101 56 2 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 102 12 4 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 103 10 3 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 104 20 3 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 105 30 7 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 106 53 6 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 107 63 5 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 108 23 2 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 109 24 4 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 101 52 3 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 102 43 3 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 103 63 7 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 104 19 6 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 105 20 5 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 106 45 2 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 107 29 4 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 108 30 3 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 109 31 3 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 101 42 7 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 102 57 6 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 103 85 5 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 104 62 2 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 105 34 4 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 106 56 3 Table 2:
PRODUCT_ID SUB_PRODUCT_ID CATEGORY EXPECTED_SALES ABSOLUTE_OR_PERCENTAGE MIN_SALES MAX_SALES EFF_FROM_DT EFF_TIL_DT PROD_1 SUB_PROD_1 CAT_1 5% PERCENTAGE 10 50 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_1 CAT_2 5% PERCENTAGE 10 50 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_1 CAT_1 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_1 CAT_2 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_2 CAT_3 20 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_2 CAT_4 10 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_2 CAT_3 10 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_2 CAT_4 5 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_3 CAT_5 10% PERCENTAGE 10 50 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_3 CAT_5 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_4 CAT_6 5 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_2 SUB_PROD_4 CAT_7 10 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_2 SUB_PROD_4 CAT_6 10 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_4 CAT_7 10 ABSOLUTE 1-Feb-23 31-Dec-99 Hi
I have attached both tables structure with sample data above.
Tables are joined on the basis of
PRODUCT_ID ,SUB_PRODUCT_ID, CATEGORY
and SALES_DATE in Table 1 falling between EFF_FROM_DT and EFF_TIL_DT in table 2 , so only matching row from Table 2 with date range as per sales date is picked. I have comeup until this point by joining both tables on key columns and filtering for SALES_DATE between EFF_FROM_DT and EFF_TIL_DT in table 2. So maximum number of rows I can have is number of rows under Table 1 (left outer join)Now what I want to achieve is for the combination of PRODUCT_ID ,SUB_PRODUCT_ID, CATEGORY and SALES_DATE get sum of TOTAL_PRODUCT_COUNT and TOTAL_SALES, which is ok and already done now a calculated column need to be created which tells me below:
DERIVED_COLUMN- which in case of if ABSOLUTE_OR_PERCENTAGE is ABSOLUTE THEN EXPECTED_SALES ELSE IF ABSOLUTE_OR_PERCENTAGE is PERCENTAGE THEN IF (EXPECTED_SALES*TOTAL_PRODUCT_COUNT) < MIN_SALES THEN MIN_SALES ELSEIF (EXPECTED_SALES*TOTAL_PRODUCT_COUNT) > MAX_SALES THEN MAX_SALES ELSE (EXPECTED_SALES*TOTAL_PRODUCT_COUNT)
PRODUCT_ID SUB_PRODUCT_ID CATEGORY SALES_DATE SALES_PERSON_ID TOTAL_PRODUCT_COUNT TOTAL_SALES PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 101 50 3 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 102 100 7 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 103 23 6 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 104 24 5 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 105 52 2 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 106 43 4 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 107 63 3 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 108 19 3 PROD_1 SUB_PROD_1 CAT_1 31-Jan-23 109 20 7 PROD_1 SUB_PROD_1 CAT_1 28-Feb-23 101 45 6 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 102 29 5 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 103 30 2 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 104 31 4 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 105 42 3 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 106 57 3 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 107 85 7 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 108 62 6 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 109 34 5 PROD_1 SUB_PROD_1 CAT_2 28-Feb-23 101 56 2 PROD_1 SUB_PROD_1 CAT_2 31-Jan-23 102 12 4 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 103 10 3 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 104 20 3 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 105 30 7 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 106 53 6 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 107 63 5 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 108 23 2 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 109 24 4 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 101 52 3 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 102 43 3 PROD_1 SUB_PROD_2 CAT_3 31-Jan-23 103 63 7 PROD_1 SUB_PROD_2 CAT_3 28-Feb-23 104 19 6 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 105 20 5 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 106 45 2 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 107 29 4 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 108 30 3 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 109 31 3 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 101 42 7 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 102 57 6 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 103 85 5 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 104 62 2 PROD_1 SUB_PROD_2 CAT_4 28-Feb-23 105 34 4 PROD_1 SUB_PROD_2 CAT_4 31-Jan-23 106 56 3 Table 2
PRODUCT_ID SUB_PRODUCT_ID CATEGORY EXPECTED_SALES ABSOLUTE_OR_PERCENTAGE MIN_SALES MAX_SALES EFF_FROM_DT EFF_TIL_DT PROD_1 SUB_PROD_1 CAT_1 5% PERCENTAGE 10 50 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_1 CAT_2 5% PERCENTAGE 10 50 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_1 CAT_1 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_1 CAT_2 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_2 CAT_3 20 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_2 CAT_4 10 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_1 SUB_PROD_2 CAT_3 10 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_1 SUB_PROD_2 CAT_4 5 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_3 CAT_5 10% PERCENTAGE 10 50 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_3 CAT_5 5% PERCENTAGE 5 20 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_4 CAT_6 5 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_2 SUB_PROD_4 CAT_7 10 ABSOLUTE 1-Jan-23 31-Jan-23 PROD_2 SUB_PROD_4 CAT_6 10 ABSOLUTE 1-Feb-23 31-Dec-99 PROD_2 SUB_PROD_4 CAT_7 10 ABSOLUTE 1-Feb-23 31-Dec-99
