1 2 Previous Next 21 Replies Latest reply on Aug 7, 2018 11:44 PM by yamuna.kosireddi

# Using one month as Constant and divide with remaining months

Hi every one,

Given below is table which shows summary view of my data.

 Month Sales Ratio(In Calculated Field) Jan 1000 1000/1200 Feb 1200 1000/1200 Mar 1300 1300/1200 Apr 2000 2000/1200

I trying to create a calculated filed for above Ratio.iam tried but its showing an error.please can any suggest how to create calculated filed for above tabel.

• ###### 1. Re: Using one month as Constant and divide with remaining months

Hi

Try this

Sum(sales)/Sum(If month = feb then sales end)

Try something like this and check the error for field name as I dont have the data

Thanks

Nipun

• ###### 2. Re: Using one month as Constant and divide with remaining months

Hi Nipun,

but still its getting an error.please suggest me.

• ###### 3. Re: Using one month as Constant and divide with remaining months

hi

can you tell what is error

Sum([sales])/Sum(If month = 'feb' then [sales] end)

if possible please send the file

• ###### 4. Re: Using one month as Constant and divide with remaining months

Hi Sailaja,

Is that what you looking for?

Try this calc,

Ratio:

IIF([Month]='Feb',{FIXED :MIN([Sales])},[Sales])/

{FIXED :SUM(IIF([Month]='Feb',[Sales],0))}

Let us know If you've any query.

Mahfooj

• ###### 5. Re: Using one month as Constant and divide with remaining months

Hi Mahfooj,

works Perfectly but i dont want in percentage it should be in decimal.can you please help me.

• ###### 6. Re: Using one month as Constant and divide with remaining months

Hi Mahffoj,

works Perfectly.Thank You Mahffoj

• ###### 7. Re: Using one month as Constant and divide with remaining months

HI Mahffoj,

In my case at febuary it is showing zero.but should be 1.please let me know.

• ###### 9. Re: Using one month as Constant and divide with remaining months

if you create Ratio measure with given code and drag it in canvas it will show in number decimal format only. Try once

If you still getting % format. Then

Just change the number format of Ratio measure from percentage to automatic. To get that Just right click on ration measure and select format. Go to pane tab and choose the format as per your wish.

Don't forget to close the thread by marking my answer as correct. So that it may help others who've similar query and your post gets invisible from the unanswered list.

Mahfooj

Sent from my iPhone

• ###### 10. Re: Using one month as Constant and divide with remaining months

Hi Mahfooj,

• ###### 11. Re: Using one month as Constant and divide with remaining months

Just apply context filter on your [Type of Fuel] dimension from filter shelf.

If you still unable to get the desired output then send me some mock up data in excel format with your expected output?

Mahfooj

• ###### 12. Re: Using one month as Constant and divide with remaining months

Sailaja - Create Calculated field like - [Sales]/{FIXED : SUM(IIF([Month]=='Feb',[Sales],0))}

• ###### 13. Re: Using one month as Constant and divide with remaining months

Can you just confirm in case of Feb your numerator will always be Jan right?

If so then try this,

Let me know If you've any query.

Mahfooj

• ###### 14. Re: Using one month as Constant and divide with remaining months

Hi Mahffoj,

Given below is table which shows summary view of my data.

 Ratio(In Calculated Field) Month Sales Jan 1000 1000/1200 Feb 1200 1200/1200 Mar 1300 1300/1200 Apr 2000 2000/1200

In Feb it should be 1.But iam getting zero.Please suggest

1 2 Previous Next