Approach to complex scenario modelling/measures

[duffby2]

I am looking for some guidance on how to go about modelling for the following type of scenario.

There are various restrictions on the source of revenue in the fact table.  These restrictions need to be assessed and assigned a pass or fail status.  Each of these restrictions cover a different combination of dimensions and so a single record can be assessed under many different restrictions. 

The desired outcome would be a single table containing one row for the assessment of each restriction, the values assessed and whether it passed or failed.

One solution I have considered is generating a table for each restriction and then appending these tables to form a consolidated view.  However, this would be onerous given that there are over 100 such restrictions to be assessed.

An example of the restrictions to be assessed:
1. No one supplier to have more than 10% of overall revenue
2. No one region to have more than 50% of revenue
3. No one product to have more than 5% of revenue for each region.

Desired output based on total revenue of 1,000 (not all rows are displayed to keep this sample brief) :

Restriction #	Product	Supplier	Region	Revenue	Limit	Status
1	*	Supplier A	*	110	100	Fail
1	*	Supplier B	*	90	100	Pass
2	*	*	USA	550	500	Fail
2	*	*	Europe	450	500	Pass
3	Product A	*	USA	80	50	Fail
3	Product B	*	USA	45	50	Pass
3	Product A	*	Europe	51	50	Fail
3	Product B	*	Europe	50	50	Pass

[v-kaiyue-msft]

Hi @duffby2 ,

1. Create a calculated column to get each supplier's percentage of total revenue.

Supplier Revenue % =
DIVIDE(
SUMX(FILTER('Table', 'Table'[Supplier] = EARLIER('Table'[Supplier]))), 'Table'[Revenue]),
1000
)

2. Return pass or fail depending on the percentage.

Restriction 1 Status = IF('Table'[Supplier Revenue %] > 0.1, "Fail", "Pass")

3. Calculate each restriction in turn.

Region Revenue % =
DIVIDE(
SUMX(FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region]))), 'Table'[Revenue]),
1000
)

Restriction 2 Status = IF('Table'[Region Revenue %] > 0.5, "Fail", "Pass")

Product Revenue % =
DIVIDE(
SUMX(FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && 'Table'[Region] = EARLIER('Table'[Region]))), 'Table'[Revenue]),
1000
)

Restriction 3 Status = IF('Table'[Product Revenue %] > 0.05, "Fail", "Pass")

4. Create the calculated column to get the final status.

Column =
IF('Table'[Restriction 1 Status] = "Pass" && 'Table'[Restriction 2 Status] = "Pass" && 'Table'[Restriction 3 Status] = "Pass", "Pass", "Fail")

5. I do not know what the table "*" means, so I will treat it as the same category to calculate, but the overall idea as described above, you can according to their own needs slightly modified.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[duffby2]

Hi Clara,

Thank you very much for your reply.  This approach would certainly work but the only concern would be the number of calculated columns I would have to create as there are over 100 such restrictions to be calculated. 

I had hoped I would be able to maintain some form of parameter table that contains all the restrctions and then have measures use the values in this "parameter table" to perform the various calculations required on the restrictions.  Not sure that is possible though. 

The "*" means all for that particular attribute - so for the first record in my table, it means that all products/regions are considered for each supplier.  Sorry, I should have have made that clearer.