Dynamic Calculation

[Sedos101]

Hi Everyone

I got a bit of a tricky one.

I have a table in Power BI that contains a bunch of mining trucks. Each one of those trucks has a payload record for each cycle it does. For example (column for Date, Cycle and Payload):

Date	Cycle	Payload
15/5/2022	1	250
15/5/2022	2	260

I have a bunch of conditions that I need to rate the payload to (Payload Distribution Index):

The Rated is: 231 tonnes, so 110% is 254.1 tonnes

120% of rated payload is 277.2 tonnes

I have set up my powerbi dashboard to have basically 3 slicer filters:

1. Date

2. Truck ID

3. Cycle number

How do I make a measure that will caclulate the Rating based on what I have selected to view in my slicers?

for example what I have selected below:

I basicaly don't know how I can apply the number of cycle payloads to the above conditions to get the rating, like I don't undertand how to write it in DAX (I am assuming it is DAX as it needs to be dynamic).

Any help would be greatly appreciated!

Thanks

[v-yalanwu-msft]

Hi, @Sedos101 ;

I have some confusion about your data scenario and the results you want to output, can you share simple examples and the results you want to output?

please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stachu]

do I understand correctly that you want to show the rating of all the selected payloads together, not the individual payload?
I would start with defining the  criteria in a separate table, something like this:

Rating	Threshold	Upper	Exceed	Max
A	1	1.1	0	1.2
B	0.95	1.1	0	1.2
C	0.9	1.1	0	1.2
D	0.85	1.1	0	1.2
E	0.8	1.1	0.02	1.2

and depending on how do you need the calculation later use it to get the rank. I expect some COUNTX to determine which payloads meet the criteria and then comparing it with the criteria table