Insightly with Power BI Desktop

[istvan_gorgey]

Dear Forum Members,

I'm suffering a bit with Insightly data connection.

I've added PowerBI data source using the Insightly connector and now I have the possibility to define measured values.

Otherwise, I can not create some multiplication.

I have a measure called 'VALUE' this contains the values for deals.

I have 'PROBABILITY' which one is not a measure but contains numbers

I would like to have something like this in coulombs:

DEAL NAME, DEAL VALUE, PROBABILITY, PROBABILITY*VALUE

The last one is not possible because it always wants me to add max, min or other calculations before probability.

Do you have any idea how to solve it?

Thanks in advance!

BR, István

[v-yulgu-msft]

Hi @istvan_gorgey,

Based on my understanding, 'VALUE' is a measure, 'PROBABILITY' is a column, now you want to create a measure whose value is PROBABILITY*VALUE, right?

If so, you have to use an aggregation function to wrap 'PROBABILITY'. As it is not supported to refer to a column that contains multiple values in a measure.

You could try this, when you add the measure to table visual, you will get the correct product.

New Measure=SUM('TableName'[PROBABILITY])*[VALUE]

Regards,

Yuliana Gu

[istvan_gorgey]

Hi Yuliana,

Thanks for the quick feedback. Yeah I tried it but it will sum all of the probability values which should be a weight instead of sum function. 

So somehow I should use a cycle or of statements to have all the value as a weight.

If you have any idea please help me.

Thanks in advance!

BR, IStván

[tonyinsightly]

Hi @istvan_gorgey,

Insightly includes a weighted value field [OPPORTUNITY_VALUE_WEIGHTED], so the calculation is already done for you. Drop that into your report, and you should be all set.

Cheers,

Tony J. Roma

Senior UX Technical Writer at Insightly

[istvan_gorgey]

Thanks but I can not calculate with that either. The problem is that I have USD and EUR values in Insightly and for those I need to calculate the exact amount.

I decided to connect to Insightly API directly from PowerBI Desktop. This will be the easiest solution so we can close this topic.

Thanks.

BR, István

[renquism]

Hi,

How were you able to connect directly to the insightly API?  I am currently trying to do this and am really struggling.  I'm receiving constant errors and there does not seem to be an easy way to do this.  My skills are very limited when it comes to connecting, so if you have any step by step or specific documentation on how you did this I would really appreciate it.  I have tried just about every solution found online but still can't come up with anything.

[istvan_gorgey]

Hi,

I actually dropped to use the out-of-the-box Insightly PowerBI Add-On because it is not useful if you would like to do some calculations.

I'm not a developer but I created a JSON code what you can use to connect Insightly's API connection (documented here: https://api.insight.ly/v2.2/Help). I used v2.1 API because with v2.2 I should use pagination to sync more than 100 data elements what would hard for me to develop... With v2.1 you do not have any limitation regarding data sync. 

Here is my code where you should paste your API KEY with Base64 encoded. This is under Query creation in Advanced Editor.

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/Opportunities", [Headers=[Authorization="Basic APIKEY", ContentType="application/json"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {
    "OPPORTUNITY_ID",
    "OPPORTUNITY_NAME",
    "OPPORTUNITY_DETAILS",
    "PROBABILITY",
    "BID_CURRENCY",
    "BID_AMOUNT",
    "BID_TYPE",
    "BID_DURATION",
    "OPPORTUNITY_VALUE",
    "FORECAST_CLOSE_DATE",
    "ACTUAL_CLOSE_DATE",
    "CATEGORY_ID",
    "PIPELINE_ID",
    "STAGE_ID",
    "OPPORTUNITY_STATE",
    "OPPORTUNITY_STATE_REASON_ID",
    "IMAGE_URL",
    "RESPONSIBLE_USER_ID",
    "OWNER_USER_ID",
    "DATE_CREATED_UTC",
    "DATE_UPDATED_UTC",
    "VISIBLE_TO",
    "VISIBLE_TEAM_ID",
    "VISIBLE_USER_IDS",
    "CUSTOMFIELDS",
    "TAGS",
    "LINKS",
    "CAN_EDIT",
    "CAN_DELETE"},{
    "OPPORTUNITY_ID",
    "OPPORTUNITY_NAME",
    "OPPORTUNITY_DETAILS",
    "PROBABILITY",
    "BID_CURRENCY",
    "BID_AMOUNT",
    "BID_TYPE",
    "BID_DURATION",
    "OPPORTUNITY_VALUE",
    "FORECAST_CLOSE_DATE",
    "ACTUAL_CLOSE_DATE",
    "CATEGORY_ID",
    "PIPELINE_ID",
    "STAGE_ID",
    "OPPORTUNITY_STATE",
    "OPPORTUNITY_STATE_REASON_ID",
    "IMAGE_URL",
    "RESPONSIBLE_USER_ID",
    "OWNER_USER_ID",
    "DATE_CREATED_UTC",
    "DATE_UPDATED_UTC",
    "VISIBLE_TO",
    "VISIBLE_TEAM_ID",
    "VISIBLE_USER_IDS",
    "CUSTOMFIELDS",
    "TAGS",
    "LINKS",
    "CAN_EDIT",
    "CAN_DELETE"})
in
    #"Expanded {0}"

If you need further assistance feel free to contact me.

BR, István

[renquism]

Thank you so much! This was exaclty what I was looking for.  One other question, how do you handle the custom fields in Insightly?  The custom fields are bundled together and when I run this query for the custom field name and the custom field value (there can be many values for custom field name), I get a null for the column.

[dan_blyth_1]

@istvan_gorgey did you ever get anywhere with the custom fields within the insightly api?

[istvan_gorgey]

, I have not really tried it.

[dan_blyth_1]

Hey @istvan_gorgey ok no problem, not sure if you also came across this issue with the insightly API but I just posted a new thread where i am looking for a solution to the limited record return on v2.2/2.3 of the insightly API, if you have a dashboard you are maintaing you may already be using the method I have developed but i am looking for a full automated solution - you can find the thread here; http://community.powerbi.com/t5/Integrations-with-Files-and/Looking-for-a-Limited-API-solution-INSIG... perhaps have a read and see if you have any ideas?

thanks,