Creating an S curve from spreadsheet consolidation

[leandro_41]

Hey, guys
I have a dataset that is a consolidation of 35 spreadsheets with their respective information.

Each worksheet has the same column structure but with information from a specific “Store”, therefore, it has a specific number of rows.

I want to create an S-curve for each of these stores, taking into account the percentage each store represents of the total and the percentage each store sold in a given time period.

I was able to make the curve, but the value is breaking down by region for example:
The south regional is 10% of the country's total and the stores of this regional 10% of the country.

I wanted the total percentage of the store, which is my smallest granularity, to be 100% and the percentage of the largest granularity to be 100% as well.

I created a calculated, to begin, column to count all the rows in the table and divide by the store but it didn't work.
QtdeTeste =
var vTotal = COUNTROWS('Manutenção_GISTM')
var vEstrutura = CALCULATE(DISTINCTCOUNT('Manutenção_GISTM'[Loja]))
return
--vTotal
divide(vEstrutura,vTotal,0)

this would be the starting point for calculating
1. The percentage of each store;
2. the percentage of the total would be a measure
Can anyone give me an idea how to solve this?

[leandro41]

Thks for return!! 

[Mahesh0016 · Accepted solution]

@leandro_41  I hope this helps you. Thank You.
 Here's how you can proceed:

1. **Calculate Store Percentage:**
Create a calculated column or measure in your data model to calculate the percentage of each store's sales contribution to the total sales. This can be done using the formula you provided:

```DAX
Store Percentage =
DIVIDE(
SUM('Manutenção_GISTM'[Sales]),
CALCULATE(SUM('Manutenção_GISTM'[Sales]), ALL('Manutenção_GISTM'))
)
```

This measure calculates the store's sales as a percentage of the total sales across all stores.

2. **Calculate Region Percentage:**
If your data includes information about the region each store belongs to, you can create a calculated column or measure to calculate the region's sales contribution to the total. Assuming you have a column called "Region" in your data, you can create a measure like this:

```DAX
Region Percentage =
DIVIDE(
SUM('Manutenção_GISTM'[Sales]),
CALCULATE(SUM('Manutenção_GISTM'[Sales]), ALL('Manutenção_GISTM'[Region]))
)
```

This measure calculates the region's sales as a percentage of the total sales within its own region.

3. **Create S-Curves:**
Now, you can use a visualization tool (such as Power BI) to create your S-curves. Place the "Store Percentage" on the Y-axis and "Region Percentage" on the X-axis. Each point on the curve will represent a store, and the curve will represent the distribution of sales across both stores and regions.

4. **Ensure Correct Scaling:**
To ensure that the curve's scaling is correct (i.e., both the store percentage and region percentage sum up to 100%), you might need to adjust the visualization settings. In Power BI, you can set the axis scales to start from 0% and end at 100% for both the X and Y axes.

By using the calculated columns or measures for store and region percentages, and by adjusting your visualization settings, you should be able to create S-curves that accurately represent the distribution of sales across stores and regions while maintaining the correct scaling. Keep in mind that the provided DAX formulas are just examples, and you may need to adjust them based on your actual data model and structure.