filter table for amount over $100

Question

Good morning, everyone,In the first table, there are 3 insurance policies. And each policy comes with different Coverage and Payments.Policy Coverage PaymentA&nbsp; &nbsp; &nbsp; &nbsp; BI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100A&nbsp; &nbsp; &nbsp; &nbsp; PD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50B&nbsp; &nbsp; &nbsp; &nbsp; BI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10B&nbsp; &nbsp; &nbsp; &nbsp; PD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20C&nbsp; &nbsp; &nbsp; &nbsp; BI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50C&nbsp; &nbsp; &nbsp; &nbsp; PD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60C&nbsp; &nbsp; &nbsp; &nbsp; PIP&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 70&nbsp;&nbsp;I am trying to generate a visualized Table under Report environment for the policies with payment over $100 and still keeps the Coverage information?For this example,&nbsp; since the total payment for Policy A and C is over $100, so the new Table with total payment over $100 will look as below:&nbsp;Policy Coverage PaymentA&nbsp; &nbsp; &nbsp; &nbsp; BI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100A&nbsp; &nbsp; &nbsp; &nbsp; PD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50C&nbsp; &nbsp; &nbsp; &nbsp; BI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50C&nbsp; &nbsp; &nbsp; &nbsp; PD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60C&nbsp; &nbsp; &nbsp; &nbsp; PIP&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 70&nbsp;Thank you very much.Dennis&nbsp;

v-chuncz-msft · Answer

12Bowers12&nbsp;,
&nbsp;
Drag measure below to&nbsp;Visual level filters&nbsp;and set Show items when the value is 1.
Measure =
IF (
    CALCULATE ( SUM ( Table1[Payment] ), ALLSELECTED ( Table1[Coverage] ) ) &gt; 100,
    1
)

12bowers12 · Answer

Thank you, SamI have tested your measure on the dataset.&nbsp;Your measure only shows 1 if the amount by individual Coverage level &gt;100.My problem is, for example, for Policy A, even the amount for Coverag BI and PD&nbsp; is &lt;=100, but the total is 150 for Policy A. Therefore, both of Coverage BI and PD for Policy A should be marked as 1.&nbsp;Dennis&nbsp;

12bowers12 · Answer

Any ideas? Thank you.

Forum Discussion

filter table for amount over $100

3 Replies

Related content

Filtered table

Dax calculate total amount ignoring filter

Can I filter the 1st fact table which then filters a 2nd fact table?

Power Query Add rows of zero amount

Filtering Table report