Limit value to sum of other value

[02welkerm71]

Hi all,

In the Visual below, I want to limit the "Sum of MI20 Counts" to the value that is in the "Sum of # Counts".  This way the last column will never go over 100%.  What would I use for this?

[RobbeVL]

Could you give some more info on the actual underlaying data? 

Also your required result...

[02welkerm71]

The "sum of # counts" column is a listing of how many times an item should be counted.  The "Sum of MI20 Counts" is how many times the item has been counted.  I don't want any item that has been counted more than it should be counted to affect the overall progress percentage.  So if an item should be counted 3 times and has been counted 5 times, I want it to display that it has been counted 3 times.

[v-diye-msft]

Hi @02welkerm71 ,

What's the detailed formula in your "sum of # counts" and "Sum of MI20 Counts"? we need to know the logic in your data. it's preferred to share your dummy pbix or simple worksheet here.

[02welkerm71]

Additionally, "Sum of MI20 counts" is calculated as:

Sum of MI20 Counts = CALCULATE(count(MI20[Plant-Matl-Stor Loc]))

[v-diye-msft]

Hi @02welkerm71 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

If you've fixed by your own, it would be much appreciated if you can share your solution here. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=IF(count(MI20[Plant-Matl-Stor Loc])>SUM(Data[# Counts]),SUM(Data[# Counts]),count(MI20[Plant-Matl-Stor Loc]))

Replace Data[# Counts] with the actual Table and column name.

Hope this helps.

[02welkerm71]

Apologies, new to this.  "sum of # counts" is just pulling a value straight from the data sheet that is unique to each "Plant-Matl-Stor Loc" (there is only one of each in the table it is pulling from).  No calculation.  "sum of MI20 Counts" is pulling from a separate table that has multiple occurences of "Plant-Matl-Stor Loc" depending on the amount of times counted.  This column uses the count of these occurances and sums them as a value.

I've attached the link to my online PowerBI worksheet.

https://app.powerbi.com/groups/me/reports/43d61311-4308-48fb-beec-1a0320339fa9/ReportSectionb54c904a...

[v-diye-msft]

Hi @02welkerm71 ,

We don't have the accessibility to view the report. could you please upload your dummy pbix to Google drive/Onedrive/dropbox to generate the sharelink to us?