Pie chart to show data from different columns with certain filters

[inglexjc]

I'm needing a pie chart to show data from 5 different columns but to show when the following filter is relevant.

Income Type <> null

Is rent correct?  <> null or Yes

Are utilities correct? <> null or Yes

Are other expenses correct? <> null or Yes

Are there other errors? <> null or no

Data:

Item ID	Income Type	Is Rent Correct?	Are utilities correct?	Are other expenses correct?	Are there other errors?
9304043879	null	Yes	Yes	Yes	No
9304917426	null	Yes	Yes	Yes	No
9306440514	Earned	Yes	Yes	Yes	No
9335837372	Unearned	null	null	Yes	No
9343613338	Earned	null	null	null	Yes

[v-ssriganesh · Accepted solution]

Hello @inglexjc,
Thank you for reaching out to the Microsoft Fabric Community Forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.


Visuals like Pie Chart, Bar/Column Chart, or Matrix will show exactly what you need, but only if you unpivot the data in Power Query and use a DAX measure. This ensures that all check types and responses are evaluated properly in the same visual.

For your reference, I’m also attaching a .pbix file so you can review the applied logic and visuals directly.

Best Regards,
Ganesh Singamshetty.

[v-ssriganesh · Accepted solution]

Hello @inglexjc,
Thank you for reaching out to the Microsoft Fabric Community Forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.


Visuals like Pie Chart, Bar/Column Chart, or Matrix will show exactly what you need, but only if you unpivot the data in Power Query and use a DAX measure. This ensures that all check types and responses are evaluated properly in the same visual.

For your reference, I’m also attaching a .pbix file so you can review the applied logic and visuals directly.

Best Regards,
Ganesh Singamshetty.

[inglexjc]

@v-ssriganesh we have the response wrong.  It should be if 

                        "Is Rent Correct?",

                        "Are utilities correct?",

                        "Are other expenses correct?"

                    = "No"

                     "Are there other errors?"   "Yes"

So I just changed your "No" to "Yes" and the "yes" to "no"  but then the visuals don't show right.

Code used: 

Valid Responses =

CALCULATE (

    COUNTROWS ( 'Data' ),

    FILTER (

        'Data',

        NOT ISBLANK ( 'Data'[Income Type] )   -- Exclude blank Income Type

            &&

            (

                (

                    'Data'[Check Type] IN {

                        "Is Rent Correct?",

                        "Are utilities correct?",

                        "Are other expenses correct?"

                    }

                    && 'Data'[Response] = "No"

                )

                ||

                (

                    'Data'[Check Type] = "Are there other errors?"

                    && 'Data'[Response] = "Yes"

                )

            )

    )

)

Only 1 check type is showing.

[inglexjc]

So with the code I put earlier Income and other errors are showing but Utilities is not and it should:

[v-ssriganesh · Accepted solution]

Hello @inglexjc,
I tested your revised logic, and I am seeing the same result you shared only “Are there other errors?” appears in the visuals. This happens because, in the current dataset, all values for Rent, Utilities, Expenses are "Yes" or blank, and your condition requires them to be "No" to be counted.

So, the measure is working as expected it’s just that the sample data does not contain "No" values for those checks.

Best Regards,
Ganesh Singamshetty.

[inglexjc]

I under stand now.  Income is blank so for that "Item" so it's not counting it.  My brain finally kicked in when I took that exclusion it's showing as needed.

Thank you!

[inglexjc]

There are no other errors.  When I create a table to show the responses you can see a "No" for income AND Utilities.  But only Incme is showing in the Pie.

Then YES for Are there other errors (so this is correct).

But the valid response formula is not picking up the response from Utilities like it should.

Here is the raw data.

Item ID	Income	Income Type	Rent	Utilities	Are other expenses correct?	Are there other errors?
9304043879	Yes	null	Yes	Yes	Yes	No
9304917426	Yes	null	Yes	Yes	Yes	No
9306440514	No	Earned	Yes	Yes	Yes	No
9335837372	No	Unearned	null	No	Yes	No
9343613338	No	Earned	null	null	null	Yes

The formula being used for Valid Responses:

Valid Responses =

CALCULATE (

    COUNTROWS ( 'Case Review Visual' ),

    FILTER (

        'Case Review Visual',

        NOT ISBLANK ( 'Case Review Visual'[Income Type] )   -- Exclude blank Income Type

            &&

            (

                (

                    'Case Review Visual'[Check Type] IN {

                        "Rent",

                        "Income",

                        "Utilities",

                        "Are other expenses correct?"

                    }

                    && 'Case Review Visual'[Response] = "No"

                )

                ||

                (

                    'Case Review Visual'[Check Type] = "Are there other errors?"

                    && 'Case Review Visual'[Response] = "Yes"

                )

            )

    )

)

[inglexjc]

@v-ssriganesh thank you so much! I did have to create a new table to match what you did but it worked!  Thank you.

[Ashish_Mathur]

Hi,

A pie can only be created from 2 columns.  In PQ, select the first column, right click and select Unpivot other columns and click on Close and Load.  Now apply your filters and create a pie chart.

[inglexjc]

Is there a visual that will do what I'm needing?

Right if I do a normal filter I'm only left with one response.  Is there a visual that will show each column response that meets that filter without being canceled out from the other filters?  They are wanting to show this all in one visual. 

Income Type <> null

Is rent correct?  <> null or Yes

Are utilities correct? <> null or Yes

Are other expenses correct? <> null or Yes

Are there other errors? <> null or no

[Irwan]

hello @inglexjc 

i might be misundersood but i assume those 5 conditions will work in OR operator so your filters seem to remove all data in that sample data, no?

The 4th condition will remove the 1st up to 4th line of your sample data.

you want to show data when "Are other expenses correct? <> null or Yes"

The 3rd condition will remove the 5th line of your sample data.

you want to show data when "Are utilities correct? <> null or Yes"

Thank you.

[inglexjc]

Right if I do a normal filter I'm only left with one response.  Is there a visual that will show each column response that meets that filter without being canceled out from the other filters?  They are wanting to show this all in one visual. 

Income Type <> null

Is rent correct?  <> null or Yes

Are utilities correct? <> null or Yes

Are other expenses correct? <> null or Yes

Are there other errors? <> null or no