'Transform and Combine' Folder Data Pull Causing Unwanted Max Column Values

[t_patts]

https://drive.google.com/drive/folders/16oFJXo9iPINyP2KlOJlgcVRfXoxmi8MX?usp=sharing

^^^ Here is my scrubbed data. I have Power BI "Get Data" ing by pulling in this folder and then summarizing the top 10 descending rows (so data is added automatically when a new data file is added each week).

Here is what it looks like thus far:

Here are my current parameters for the line and stacked column chart:

 

(The proportion by week is renamed but its the % GT of count for equipment description of the combined files)

I know the selection method (folder > combine content) is the reason the total weekly percent is not 100, its X% of all of the items in all of the files being combined.. I would like to have it look like 

where each week has 100% of each weeks items (seperate from other weeks), not X% of total items from alls weeks spreadsheets.

Can I do this while still pulling in new files each week automatically or will I have to manually pull the files to keep the items seperate? What can I do to get each week as its own set?

[v-chenwuz-msft · Accepted solution]

Hi @t_patts ,

Create a measure like this:

Measure = 
VAR _Count = COUNTROWS('Table')
VAR _ALL = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[WW]))
RETURN
DIVIDE(_Count,_ALL)

And replace proportion by week by this measure.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@t_patts , Are you using 100% stacked visual place of stacked visual ?

or your calculation has some issue

create a measure for that

Percent of SubTotal or Total: https://www.youtube.com/watch?v=6jTildcV2ho&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=37