12 Replies Latest reply on Aug 9, 2018 9:13 AM by Edwin Niculaescu

Measures in Table

Hello,

Sorry for the newbie questions.

Here is a example data set:

Date     Payment     Charges

April     \$100               \$200

May     \$200               \$300

June     \$300               \$400

How do I create a table like this:

April     May     June

Charges          \$200     \$300     \$400

Payments        \$100     \$200     \$300

%                    50%     66%         75%

Thanks!

Edwin

• 1. Re: Measures in Table

see below

results in this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• 2. Re: Measures in Table

Jim, thanks for the reply but payments and charges are their own column. In my data, I don't have a "Category" column that includes payments and charges. Hope that makes sense....

• 3. Re: Measures in Table

sure it makes sense

That data structure is something that results in lots of calculated and lots of extra work -

see the link   Pivot Data from Columns to Rows

Pivoting the data will make it easier to work with

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

2 of 2 people found this helpful
• 4. Re: Measures in Table

Hello Edwin,

You can first go to your Data Source tab, Select both the Payment and Charges columns and select Pivot.  Then give the category a name.  This will also create one combined measure for both Payment and Charges and one dimension that will then allow you to follow Jim's directions above.  Hope this helps!

2 of 2 people found this helpful
• 5. Re: Measures in Table

Thanks, I was able to pivot the two columns and got it to work!

Any ideas on how to add the calculation of payments /charges as a additional row?

• 6. Re: Measures in Table

If Payments is a measure and Charges is a measure then create a calculated field:

sum([Payments])/sum([Charges])

set the default formatting to percentage then add it to the sheet (ie drag to the Measure Values shelf)

1 of 1 people found this helpful
• 7. Re: Measures in Table

Hi Chris,

Payments and Charges are not measures as I had to pivot the data per the comments above.

• 8. Re: Measures in Table

I was worried that might be the case

You can use the same logic, but it will be more complicated and you won't be able to place it in the sheet as easily.

Although I would suggest having Payments & Charges as Measures is a better option, that's what they are anyway.

• 9. Re: Measures in Table

Hi Chris,

Below is my layout. I had to delete portions of the values due to sensitivity of data.

You mentioned I could get this table without pivoting the data?

• 10. Re: Measures in Table

Hello Edwin,

After the Pivot and formatting in your dashboard you can then separate the measures by creating two calculated fields:

Payments Custom Calc

IF [Category] = "Payments" THEN [Pivot Field] ELSE 0 END

Charges Custom Calc

IF [Category] = "Charges" THEN [Pivot Field] ELSE 0 END

You will now have two separate measures to then create step Chris mentioned above.

Hope this helps!

1 of 1 people found this helpful
• 11. Re: Measures in Table

But that's essentially unpivotting the data again

This is what happens when you don't pivot the data and use the calculation I mentioned above:

2 of 2 people found this helpful
• 12. Re: Measures in Table

Chris, this solution is great. Thank you!