Help to create a dashboard

[rajibmahmud]

Hi everyone, I am a noob in Power BI. Used excel so far. I am trying to create a sales dashboard, where I need to calculate some complex data (Atleast for me). Can anyone help with below? If needed I can share sample data.

1. How to calculate contribution in BI itself from a set of data

2. For sales data how to calculate growth, where I would like to see below

a. Current Year Sales/Prior Year Sales

b. Current Year Sales for a particular Product/Prior Year Sales for a particular Product from a large set of data

3. Preparation of Scatter Chart with 3 type of calculated data from big set of data

a. Sales, Growth and Contribution -  All of this have to be calculated

Also please share tutorial link to create relation between table and use in dashboard. If I am not wrong this option is similar to same feature of PowerPivot

[Greg_Deckler]

Going to need sample data. You could also download the dashboard in an hour and dashboard in a day material and this will walk you through working with sales data to build reports and dashboards.

[rajibmahmud]

Attaching 2 set of sample data.

http://www.fileconvoy.com/dfl.php?id=g30fb6f71eacc7da9999963241a1d1b182f0a737bc

 Also, where can I find the material you reffered? 

[rajibmahmud]

Bump!

[v-huizhn-msft]

Hi @rajibmahmud,

In Power BI desktop, click the Get Data on home page->Excel, import your resource table into Power BI desktop. You can create measure and calculated columns like Power Pivot. And the DAX in Power Pivot is also correct in BI.

1. How to calculate contribution in BI itself from a set of data

What's your of this problem? How to calculate contribution based on your sample data?

2. You can create a Calendar Tbale, and create a relationship between sales Table and Calendar Table. 

a. Current Year Sales/Prior Year Sales

Total Sales = SUM(Sales[SALE])
Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE])
Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))

b. You can add a filter like FILTER(Sales,Sales[Product]="UK").

Total Sales = CALCULATE(SUM(Sales[SALE]),FILTER(Sales,Sales[Product]="UK"))
Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE])
Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))

3. Create a Scatter Chart, select the corresponding measure or calculated columns as fields. Because of your data is part, I am unable to reproduce your scenario. 

For creating relationship between tables, please review this article.

Best Regards,
Angelia

[rajibmahmud]

Hi @v-huizhn-msft

Thanks. 

Regarding Growth calculation,

Suppose I would like to see growth from below table. Adding custom column will help? As i would like to see growth of Product A or B seperately or Total Growth based on selection criteria. Also, there would be scenario where I would like to see Sales and Proft growth from same data set.

Year	Measure	Product	Value
2016	Sales	A	10
2016	Sales	A	20
2017	Sales	B	30
2017	Sales	B	40
2016	Profit	A	5
2016	Profit	A	10
2017	Profit	B	15
2017	Profit	B	20

 Also, do I hae to add calculated column for each measure, as I will be needing many such calculated number. What I am wondering is, will it be possble to create another table in BI itself where all the calculation will be done.

Regarding : 1. How to calculate contribution in BI itself from a set of data

The issue is the same. I am trying to calculate

- Discount % on Sales for a product or multiple product or Product Region etc combination

[v-huizhn-msft]

Hi @rajibmahmud,

In Power BI, we can create a new table by clicking "New Table" under Modeling on Home page. Not all measure can transfer to calculation when create new table. The new table is stationary, it will not effected by the filter.

Best Regards,
Angelia