Does calculate work with virtual table/columns

[saad_mce]

Hi all,

I am trying to measure how many products in a factory achieved the production plan in a certain month. Monthly production plan table is divided into daily plans in a different table. For the measure, I tried to use virtual table. 

I have written two formulas using a virtual table which work fine on a row level. But they don't work on the aggregate level in the pivot table. Please see the output of the pivot table as below. The formulas/measures giving wrong answers at the grand total level are shown in Red. The individual material level answers are fine.

Pivot Table:

Start of Month	February 2022
Material Code	Material Description	Sum of Qty/Day	Sum of Actual Production	Plan Achievement	Plan Achievement2	SKUs Achieved 1
10420	A	8,640	8,640	100.00%	100.00%	1
10623	B	10,560	9,600	90.91%	90.91%	1
10624	C	28,800		0.00%		0
10628	D	28,800	14,400	50.00%	50.00%	0
10630	E	2,880		0.00%		0
Grand Total		79,680	32,640	40.96%	204.82%	5
				Expected results	40.96%	2

I have written 3 formulas. One of which works at row and aggregate level. This is shown below. This is not based on virtual table. This one is marked in blue in the above pivot table.

Plan Achievement:=IF(AND(SUM('ZLTP Prod Jan 22 Daily'[Qty/Day])>0,ISBLANK(SUM('Actual Production'[Actual Production]))),0,IFERROR(SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]),0))

The other two formulas that are based on virtual tables are as below. I need the virtual table because I dont want to add the plan achievement column physically in the daily production tables. This will complicate the tables as one product can be produced for several days and the plan achivement is calculated on the total monthly production number. So plan achivement will be repeated for several rows.

The sumx formula works fine on the virtual table. I suppose I need to use calculate in place of sumx to get the aggregate right as well. The calculate is not working on the columns of virtual table. Calculate probably needs a physical column in a physical table.

Is there a different way to approach this problem?

Plan Achievement2:=SUMX(ADDCOLUMNS(SUMMARIZE('ZLTP Prod Jan 22 Daily','ZLTP Prod Jan 22 Daily'[Resource],'ZLTP Prod Jan 22 Daily'[Material],'ZLTP Prod Jan 22 Daily'[Family Desc]),"Plan Achievement2",SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]),"SKUs achieved",IF((SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]))>0.8,1,0)),[Plan Achievement2])

SKUs Achieved 1:=SUMX(ADDCOLUMNS(SUMMARIZE('ZLTP Prod Jan 22 Daily','ZLTP Prod Jan 22 Daily'[Resource],'ZLTP Prod Jan 22 Daily'[Material],'ZLTP Prod Jan 22 Daily'[Family Desc]),"Plan Achievement2",SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]),"SKUs achieved",calculate(IF((SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]))>0.8,1,0))),[SKUs achieved])

I have the below Data model. The dimension table are Machines, MSAUDIT, New MSAUDIT and Calendar. These 4 dimension tables are filtering the fact tables Actual production, ZLTP Prod Feb 22 Daily, ZLTP Prod Jan 22 Daily, ZLTP Prod Mar22, ZLTP Prod Feb 22 and ZLTP Prod Jan 22.

Data Model:

Any help to solve this problem will be greatly appreciated. 

Regards,

Abrar

[tamerj1 · Accepted solution]

Hi @saad_mce 
To be honest I did not fully read your requirement. Only the part the states the problem with the grand total. I did not understand the need for the virtual table in the "Plan Achievement2" measure Why don't you just use the calculation straight away? 

Plan Achievement2 :=
SUM ( 'Actual_Production'[Actual Production] )
    / SUM ( 'ZLTP_Prod_Jan22_Daily'[Qty/Day] )

For the other measure measure just use

SKUs Achieved 1 New:=SUMX ( VALUES ( New_MSAUDIT[Material] ), [SKUs Achieved 1] )

[saad_mce]

Hi @tamerj1,

Thanks for your quick response.

As I was using virtual table for "SKUs achieved 1" measure, I thought I would try this for "Plan Achievement" as well. This is to get my head around the virtual table concept. I am happy to use the formula as you suggested.

Your suggestion is great for the grand total. Your recommended formula works perfect.

I was thinking if I could avoid the two measures to get the numbers right for SKU level and grand total.

I was wondering if I create a variable for "SKUs Achieved 1" inside the "SKUs Achieved 1 New". For sume reason, this approach is not working. The sum is still not correct.

Anyway, if this can not be fixed, I will just use the two step measure.

Thanks again for your suggestions and have a great weekend.

SKUs Achieved 1 new:=VAR SKUsAchieved1=(SUMX(ADDCOLUMNS(SUMMARIZE('ZLTP Prod Jan 22 Daily','ZLTP Prod Jan 22 Daily'[Resource],'ZLTP Prod Jan 22 Daily'[Material],'ZLTP Prod Jan 22 Daily'[Family Desc]),"Plan Achievement2",SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]),"SKUs achieved",calculate(IF((SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]))>0.8,1,0))),[SKUs achieved]))

Return
SUMX ( VALUES ( 'New MSAUDIT'[Material]), SKUsAchieved1)

[saad_mce]

Hi,

I am tagging top kudoed authors of last month in this post.

@tamerj1, @amitchandak, @johnt75, @Jihwan_Kim : I hope you don't mind me tagging you in this post. I would appreciate any advice from you if you have time. Apologies for this unsolicited approach. 

Regards,

Abrar  

[saad_mce]

Hi @v-yetao1-msft,

Please see sample data, data model and formulas in the below link to the excel file. The dax formulas are in the 'ZLTP_Prod_Jan22_Daily' page in the data model.

Please download the excel file to see the data rather than opening in googlesheet. The pivot table results are in sheet1.

Regards,

Abrar

https://docs.google.com/spreadsheets/d/1dOdjvZDNQ2Y_GbPVkLU9KIaoWqWPNeu4/edit?usp=sharing&ouid=10840...

[v-yetao1-msft]

Hi, @saad_mce 

For me, it is a bit difficult to restore your scene, can you provide your sample(remove sensitive info) ? It may help us to deal with your problem easier .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.