Calculation Context in Power BI desktop while creating new Measure

[saurabhg]

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!

[saurabhg]

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)

[saurabhg]

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

[saurabhg]

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

[saurabhg]

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

[saurabhg]

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

[Sahir_Maharaj]

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.

[saurabhg]

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 

[v-yiruan-msft]

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

[saurabhg]

Hi All,

any help on this topic please?

Let  me know if the issue or required calculation is not clear with my post?

My ask is to perform any calculation not at row level (at each rows of raw data) but to sum few values on the basis of grouping columns and then for the same group get unique value of another measure which is used for creating some calculated variables.

Regards,

Saurabh Gupta