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.

 

[scottsen]

Ya... this is the main downside to calculated columns... they are totally static. They ONLY recalculating when refreshing from your data sources. When you need dynamic behavior... it must be done in a measure (calculated field).

If you can share out your workbook, I can try to help w/ a measure version.

 

[tusharmehta]

I don't have permission to attached file or I am not sure if any setting i need to do to enabled it, if any other way to send file then let me know may be email or some other dropbox etc ?

 

[tusharmehta]

https://drive.google.com/file/d/0B_c8gU3TDT4rLUJtcDlKa3RJMWc/edit?usp=sharing

I had attached file to my google drive, I am not sure it is allow or ok or not but to avoid email I am trying some option.

 

[scottsen]

Links to google docs are fine.

So, I got you... closer. But it's a bit of a tricky problem, and we didnt exactly solve it. Here are the measures I wrote:

[TotalSales] :=CALCULATE(SUM(TransactionTable[SalesAmount]), TransactionTable[DocType] = "SAL")

[MinBudget] := CALCULATE(SUM(SalesRange[MinValue]),
     FILTER(SalesRange, SalesRange[ShopCode] = FIRSTNONBLANK(TransactionTable[ShopCode], 1)),  
     SalesRange[Period] = "Daily")

[MaxBudget] := CALCULATE(SUM(SalesRange[MaxValue]),
     FILTER(SalesRange, SalesRange[ShopCode] = FIRSTNONBLANK(TransactionTable[ShopCode], 1)),  
     SalesRange[Period] = "Daily")

[Status] := if([TotalSales] <  [MinBudget],  -1,
     if ([TotalSales] > [MaxBudget], 1,0))

It's not super clear what Total / Grant Total columns should look like... it's not really possible to calculate the proper budget without a separate Calendar table to know which days you SHOULD sell on. Those columns are currently ... incorrect.

 

[tusharmehta]

It looks better then before regarding total I will have to think about some other option.

Once again I am very impressed the way you are looking into the problem and trying to get some short of solution.

As I had mentioned I am beginner and we both are living in different zone else I could offer you to teach me step by step examples.

I had purchased study martial from MrExcel website and going though that and I hope it will help me to understand basics.

Thanks once again I will continue my study and if any doubt I will post here.

 

[tusharmehta]

I AM VERY THANKFUL to MREXCEL website.

It is helping lots to beginner like me.

Help of forum and study materials I am able to clear my doubts and getting better understanding about PowerPivot basics.

ALL the guys who are part of it need to be highly appreciate for their efforts.