How do I associate a measure created in the app to a dimension so it displays in a pivot table chart correctly?

[jmurrevcyc]

I am trying to figure out how to associate a measure that was created after the data was loaded with a dimension that can be grouped into a source that was created during the load script for other measures.

Or must the benefit calculation be done in the load script?

Consider the following:

[Oleg_Troyansky]

Hi,

I think you are confusing the terms a bit. The data is always loaded and associated in the data load script, and the Measures are always calculated in charts - even if you did some preliminary calculations in the script.

Measures typically contain some aggregation functions, such as sum(), count(), min(), max(), etc.

So, the calculation for Goal might look like "Sum(Goal)" and the calculation for Benefit might look like sum(Benefit), and both data elements should be associated to your dimensions - Category, Month, and Region.

If you share a sample app, I could take a look and see what might be wrong there...

Cheers, 

[jmurrevcyc]

Oleg -

Thank you for the information and rapid response. I had to mock up the question for anonymity, so unfortunately I don't have a sample app to share. That said, the benefit calculations for the nails is a bit complex, and involves taking ratios from multiple sources and * by a value from another source:

My takeaway here is I need to do the calculation in the load script.

The benefit calc for nails looks something like this:

(prioryear sales / prioryeargoal )- (currentyear sales / currentyeargoal) 

this is the change and is then multiplied by the currentyear grossrev to get the benefit. all components come from different sources of data. That data is currently loaded into the script editor using concatenate fact with each source name being unique.

py - we load in the ytd % by cust

cy - we calc current sales / current goal

[Oleg_Troyansky]

I don't think that calculating your measures in the script is the right answer here. These kinds of calculations are almost always performed in charts, and they can be sensitive to user selections etc... However, the more you obfuscate your question, the more difficult it is for us to help you. You can explain your problem using mock-up data entities, but you should use specific technical terms and formulas, even if you are counting apples and bananas. Otherwise, it's a waste of your time.

[jmurrevcyc]

Oleg - Here is the code that calculates the benefit for the Nail Sales

if
(
cust_active = 0,0,

(
(
//get all payments from Nail Sales Revenue
SUM (
{<
Source={'nail_cash_hist','nail_cash_cur'}//,
>}[value]
)
/
//get all revenue from Nail Sales
SUM (
{<
Source={'net_rev_hist','net_rev_cur'}
>}[YTD Goal]
)
)
-
//get last year's ratio of payments / revenue (baseline% value)

SUM (
{<Source={'NAILBASE'}
>}value
)
)

*
//get all revenue from Nail Sales
SUM (
{
<Source={'net_rev_hist','net_rev_cur'}
>}[YTD Goal]
)

)

[jmurrevcyc]

What makes this challenging is I am trying to mirror a pivot table in excel that displays this data. And the pivot table shows the individual categories (nails, hammers, chainsaws) as rows, and then has the regions up top. The measures are columns, and if we don't have a dimension row, the columns display the data just fine. Each column is named appropriately, but at the end of the day the appearance is different than how Excel displays it.

So, if I remove the source row dimension then the data is displayed like this

Because these are measures, I don't think I can make a row out of them, right?

[jmurrevcyc · Accepted solution]

Hello all -

I am going to close out this question, as I have created a solution that works. The main issue is that there is no way to create a measure and associate a category that can be placed as a dimension row in a pivot table chart without creating that measure, and dimension in the load script. 

To clarify, you can use any measure as a column in the pivot chart, and those measures can be labeled to mean the same thing. That is not the issue for this problem.  The issue is associating it with a row in the pivot chart.

After I put the measures in the load script, I was able to associate a dimension with those values, that I was then able to add to the pivot chart as a row.

Thanks to all who have viewed, or tried to provide a solution for this. And if I am mistaken, and there is a way to do this, feel free to add your thoughts.

Thank you,

jmurrrevcyc