How to use If,Switch or calcuate with PowerPivot

[tusharmehta]

Hello,

I had start learning powerpivot from last few days and generating some test example so I can learn it's concept and features.

During my test I came to now small doubt in which I did not understand which logic statement will be more suitable.

I am here with sending my test table and question what I have.

--------------------
Custom Range Table
--------------------

[TABLE="width: 297"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ShopCode[/TD]
[TD]Period[/TD]
[TD]MinValue[/TD]
[TD]MaxValue[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Daily[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]25000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Monthly[/TD]
[TD="align: right"]310000[/TD]
[TD="align: right"]750000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Anually[/TD]
[TD="align: right"]3720000[/TD]
[TD="align: right"]9300000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]Daily[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]25000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]Monthly[/TD]
[TD="align: right"]310000[/TD]
[TD="align: right"]750000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]Anually[/TD]
[TD="align: right"]3720000[/TD]
[TD="align: right"]9300000
[/TD]
[/TR]
</tbody>[/TABLE]


--------------------
Transaction Table
--------------------
[TABLE="width: 395"]
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD]ShopCode[/TD]
[TD]DocNo[/TD]
[TD]DocDate[/TD]
[TD]DocType[/TD]
[TD]QTY[/TD]
[TD]SalesAmount[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]31/12/2013[/TD]
[TD]PUR[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12000[/TD]
[/TR]
</tbody>[/TABLE]

--------------------
Expected result
--------------------

[TABLE="width: 267"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ShopCode[/TD]
[TD]Date[/TD]
[TD]SalesAmount[/TD]
[TD]SalesStatus[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]26000[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]2/2/2014[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

------------
Purpose :
------------

salesstatus result value will help to generate KPI status
Custom Range Table will help user to change target values when it is needed

------------
Questions:
------------

How to generate decided result, which logical statement will be better to use it (If, switch or calculation ) and how ?

Kindly let me know if I am missing anything while explaining my concept.

Thanks in Advance.

 

[tusharmehta]

To make more simple I am updating thread:

I would like to add new salesstatus field which will indicate the number.

If Transaction table salesamount is less then custom range MinValue then number will be -1.

If Transaction table salesamount is greater then custom range MaxValue then number will be -1.

And Finally

If Transaction table salesamount will be between MinValue and MaxValue then number will be 0.


To test nested IF I done initial testing but get error:

=if (TransactionTable[SHOPCODE]=SalesRange[ShopCode] && SalesRange[Period]="Daily" ,1,0)

Error:
The value for column 'ShopCode' in table 'SalesRange' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

Plan was to follow like below mention statement:

=if (TransactionTable[SHOPCODE]=SalesRange[ShopCode] && SalesRange[Period]="Daily" && TransacionTable[SalesAmount]<SalesRange[MinValue],-1,
if (TransactionTable[SHOPCODE]=SalesRange[ShopCode] && SalesRange[Period]="Daily" && TransacionTable[SalesAmount]>SalesRange[MaxValue],1,0)
)

Kindly please anyone check and guide me for same.

Thanks.

 

[scottsen]

=if (TransactionTable[SHOPCODE]=SalesRange[ShopCode]...

The cryptic error is basically telling you "uhh... I dunno WHICH row in the SalesRange table you want me to use".

When evaluating a calculated column, Power Pivot will iterate 1 row at a time, and in that case you can reference specific columns of the table... without an aggregator (sum,average,min,etc). But you can't run off to some OTHER table and do the same. Even if YOU know there is only 1 row that will match, you must apply an aggregator (and in that case, it won't matter which).

Does that make sense?

 

[tusharmehta]

Thank sscottsen,

I am trying to understand your reply but I think I am not able to understand fully.

What I have understand that you are suggesting me to use calculate with aggregate? if so I am not sure how I can use aggregate on below table.

----------------------------------------------------------------------------------------------------------
What I am trying to get one extra filed on transaction table (SalesStatus) and it will be depend on salesamount.
----------------------------------------------------------------------------------------------------------

[TABLE="width: 583"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]DocNo[/TD]
[TD]DocDate[/TD]
[TD]DocType[/TD]
[TD]QTY[/TD]
[TD]SalesAmount[/TD]
[TD]SalesStatus[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]-1[/TD]
[/TR]
</tbody>[/TABLE]


I want to filter only single from range table and base on salesamount it will give me salesstatus, in above example salesamount is less then minvalue so status will be -1.

[TABLE="class: cms_table, width: 297"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]Period[/TD]
[TD]MinValue[/TD]
[TD]MaxValue[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Daily[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]25000[/TD]
[/TR]
</tbody>[/TABLE]

However I will start looking for calculation filed and see i can get required result.

 

[scottsen]

If you want to share your workbook, it would be a bit easier to help.

Do you have a relationship between your tables on the ShopCode column? If so, I would remove the "TransactionTable[SHOPCODE]=SalesRange[ShopCode]" as it is going to follow your relationship (and always be true) anyway. Then I suspect you just need to use RELATED() on some stuff ... eg: RELATED(SalesRange[Period])="Daily"

 

[tusharmehta]

Once again scottsen thanks,

I don't have relationship,

Regarding attaching the file I was looking into post but I did't found so not sure how to do that.

 

[tusharmehta]

scottsen,

Once again I am copying both tables and those tables you may copy into excel and link table in powerpivot.

TransactionTable

[TABLE="class: grid, width: 494"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]DocNo[/TD]
[TD]DocDate[/TD]
[TD]DocType[/TD]
[TD]QTY[/TD]
[TD]SalesAmount[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]1/1/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]31/12/2013[/TD]
[TD]PUR[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]1/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]1[/TD]
[TD]1/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26000[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]2[/TD]
[TD]2/2/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12000[/TD]
[/TR]
</tbody>[/TABLE]

SalesRangeTable

[TABLE="class: grid, width: 344"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ShopCode[/TD]
[TD]Period[/TD]
[TD]MinValue[/TD]
[TD]MaxValue[/TD]
[/TR]
[TR]
[TD="align: right"]ABC[/TD]
[TD="align: right"]Daily[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]25000[/TD]
[/TR]
[TR]
[TD="align: right"]ABC[/TD]
[TD="align: right"]Monthly[/TD]
[TD="align: right"]310000[/TD]
[TD="align: right"]750000[/TD]
[/TR]
[TR]
[TD="align: right"]ABC[/TD]
[TD="align: right"]Anually[/TD]
[TD="align: right"]3720000[/TD]
[TD="align: right"]9300000[/TD]
[/TR]
[TR]
[TD="align: right"]BCD[/TD]
[TD="align: right"]Daily[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]25000[/TD]
[/TR]
[TR]
[TD="align: right"]BCD[/TD]
[TD="align: right"]Monthly[/TD]
[TD="align: right"]310000[/TD]
[TD="align: right"]750000[/TD]
[/TR]
[TR]
[TD="align: right"]BCD[/TD]
[TD="align: right"]Anually[/TD]
[TD="align: right"]3720000[/TD]
[TD="align: right"]9300000[/TD]
[/TR]
</tbody>[/TABLE]

I don't have any relationship between two table.

I hope now you will be able to understand in better way ?

 

[scottsen]

Let's see how this works for you. I created 3 calculated columns on the Transaction Table:

[BudgetMin]
=CALCULATE(min(SalesRangeTable[MinValue]), FILTER(SalesRangeTable, SalesRangeTable[ShopCode] = TransactionTable[ShopCode] && SalesRangeTable[Period] = "Daily"))

[BudgetMax]
=CALCULATE(min(SalesRangeTable[MaxValue]), FILTER(SalesRangeTable, SalesRangeTable[ShopCode] = TransactionTable[ShopCode] && SalesRangeTable[Period] = "Daily"))

[SalesStatus]
=if(TransactionTable[SalesAmount] <  TransactionTable[BudgetMin], -1,
     if (TransactionTable[SalesAmount] > TransactionTable[BudgetMax], 1, 0))

Howz that workout?

-Scott

 

[tusharmehta]

Scott, It worked as expected.

I am very thankful to you for making me understand that how efficiently we can use calculate.

Great way of explanation, appreciate for same..

 

[tusharmehta]

Scottsen,

While doing further testing I have one more trouble on calculated column which I had created earlier.

Below mentioned calculated column work correctly when I have SINGLE INVOICE AND SINGLE PRODUCT, But if I have SINGLE INVOICE AND MULTIPLE PRODUCT it will calculate status column while you generated report for single day.

As I had understand earlier I had created below mentioned calculated columns:

[BudgetMin]
=CALCULATE(min(SalesRange[MinValue]), FILTER(SalesRange, SalesRange[ShopCode] = TransactionTable[ShopCode] && SalesRange[Period] = "Daily"))

[BudgetMax]
=CALCULATE(min(SalesRange[MixValue]), FILTER(SalesRange, SalesRange[ShopCode] = TransactionTable[ShopCode] && SalesRange[Period] = "Daily" ))

[SalesStatus]
=if(TransactionTable[SalesAmount] <  TransactionTable[BudgetMin] && TransactionTable[DocType]="SAL", -1,
if (TransactionTable[SalesAmount] > TransactionTable[BudgetMax] && TransactionTable[DocType]="SAL", 1,0))

[TABLE="class: grid, width: 573"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]DocNo[/TD]
[TD]Products[/TD]
[TD]DocDate[/TD]
[TD]DocType[/TD]
[TD]QTY[/TD]
[TD]SalesAmount[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Prd0001[/TD]
[TD]1/1/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Prd0002[/TD]
[TD]1/1/2014[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 344"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]Period[/TD]
[TD]MinValue[/TD]
[TD]MaxValue[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Daily[/TD]
[TD]10000[/TD]
[TD]25000[/TD]
[/TR]
</tbody>[/TABLE]

Output with calculated column (As expected):

[TABLE="class: grid, width: 724"]
<tbody>[TR]
[TD]ShopCode[/TD]
[TD]DocDate[/TD]
[TD]DocNo[/TD]
[TD]Products[/TD]
[TD]DocType[/TD]
[TD]QTY[/TD]
[TD]SalesAmount[/TD]
[TD]BudgetMin[/TD]
[TD]BudgetMax[/TD]
[TD]SalesStatus[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]
[TD]Prd0001[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]25,000[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1[/TD]
[TD]Prd0002[/TD]
[TD]SAL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]25,000[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]



Shopcode, Product Wise PivotTable (As exacted without ground total)

[TABLE="class: grid, width: 273"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]SalesAmount[/TD]
[TD]Testing Value[/TD]
[/TR]
[TR]
[TD]Prd0001[/TD]
[TD="align: right"]108000[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]Prd0002[/TD]
[TD="align: right"]26000[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]134000[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Problem when I generate pivot table using date it will sum the Testing Value (SalesStatus)

[TABLE="class: grid, width: 273"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]SalesAmount[/TD]
[TD]Testing Value[/TD]
[/TR]
[TR]
[TD]01/01/2014[/TD]
[TD="align: right"]35,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]35,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


I guess it happens because we created calculated column instated of measure, what I understand that if we created measure then it will automatically calculate value base on selected criteria ?

In above mentioned case if TestingValue (SalesStatus) is getting calculated base on selected criteria, (date or any other) then I guess we can get expected result ?

I hope my explanation are easy to understand ?