Adding measures from different tables

#1

Hi, relatively new to Dundas and need help with a dashboard.

I need to show our company’s sales for a given period. We have two different types of sales invoices (job card invoices and sales order invoices) and we also have two different types of sales credit notes (job card credit notes and sales order credit notes). I can create the table for each of these four different measures but I do not know how to just show our net sales (which would the four measures added together) as one amount.

The job card invoices and sales order invoices and job card credits and sales order credits are all in different tables.

Tommy
Ireland.

#2

Hi Tommy

If you could do this in a data cube, that would be ideal. If you can use a union or join to get them into the one table that would be simplest. If it is a union then you just need to add them, if it is a join then you can add all the measures into a data visualization and create a formula measure to sum them.

Otherwise you could use a formula visualization to do it on the dashboard. Basically, you put the four tables on the dashboard and then add a formula visualization. In the formula bar, click on all four tables and put a plus sign between them.
Once this is done, the formula visualization will show your value and you can then delete the four tables or just drag them to outside the canvas.

Hope that points you in a useful direction

2 Likes
#3

Hi Tommy,

Thank you posting your query on the user forum.

You can obtain the net sales from the 4 different measures in the different tables by using “formula visualization”. Please find the steps below:

  1. Drag the 4 measures from the tables into a dashboard.

  2. Go to Toolbar -> Click on Data Visualization -> Click on Formula Visualization

  3. A formula bar will appear. You can click on any measure and add “+” sign to do the summation. Dundas BI will automatically select the measures. You can also manually enter the measures by typing the “$” sign. Once done, click apply.

  4. On Apply, a formula visualization will appear on the dashboard. You can remove the unwanted fields from the visualization by clicking on the “cross” button against each measure.

  5. You can also change the visualization to a “table” by right clicking on the visualization -> selecting “Revisualize” and then a “Table”. You can also do this from the Toolbar by clicking on “Revisualize”.

For more detailed overview on Formula Visualizations, you can check the following support link: https://www.dundas.com/support/learning/documentation/design-view/formulas/using-a-formula-visualization

Alternatively, you can also create a data-cube with the 4 tables and use a calculated element. You can get a detailed overview by checking the following links:
https://www.dundas.com/Support/learning/videos-tutorials/data-cubes/introduction-to-data-cubes
https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/transforms/common/calculated-element

Please do let us know if you have any further questions.

Best Regards,
Nishant

1 Like