create a pie chart wth calculated legend

[Anonymous]

Hello everyone ,

I have to display in pie chart the Number of hosts per number of conference bracket,

Fot that i do not have a legend and measure ,

legend should be displayed as this way ;

if count(conf id ) per host <4 ==>0-4 meetings

if count(conf id) per host between 4 and 10 ==> 4-10meetings

if count(confid) per host >10==> >10meetings

so in the legend i shouad have 3 items : 0-4 meetings    4-10meetings   10meetings 

and in the value of my pie chart i shuld have the number of host per number of the conference brackets

How can i do this please , i don't know how to create my legend

[VahidDM · Accepted solution]

Hi @Anonymous 

Try to add a new column to your table with this code:

Count ID per Host =
VAR _Confid =
    FIRSTNONBLANK ( 'Table'[confid], "" )
VAR _Host =
    FIRSTNONBLANK ( 'Table'[Hostid], "" )
VAR _Count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Hostid] = _host && 'Table'[confid] = _Confid )
    )
RETURN
    SWITCH (
        TRUE (),
        _Count < 4, "0-4 meetings",
        _Count > 4
            || _Count < 10, "4-10 meetings",
        _Count > 10, "10 meetings"
    )

Then use that new column in your pie chart as a legend:

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

Appreciate your Kudos✌️!!

[VahidDM]

Hi @Anonymous 

Can you share a sample of your data in a table format here:

Appreciate your Kudos✌️!!

[Anonymous]

Hostid	confid
shinigalo@gmail.com	122333
sivkya.loli@gmail.com	122699
shinigalo@gmail.com	123666

[VahidDM · Accepted solution]

Hi @Anonymous 

Try to add a new column to your table with this code:

Count ID per Host =
VAR _Confid =
    FIRSTNONBLANK ( 'Table'[confid], "" )
VAR _Host =
    FIRSTNONBLANK ( 'Table'[Hostid], "" )
VAR _Count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Hostid] = _host && 'Table'[confid] = _Confid )
    )
RETURN
    SWITCH (
        TRUE (),
        _Count < 4, "0-4 meetings",
        _Count > 4
            || _Count < 10, "4-10 meetings",
        _Count > 10, "10 meetings"
    )

Then use that new column in your pie chart as a legend:

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

Appreciate your Kudos✌️!!

[Anonymous]

what have you used for the value in your bar chart , is it a measure ?

[VahidDM · Accepted solution]

In a Pie chart, I used the Hostid column and changed the aggregation to count.

Appreciate your Kudos!!

[Anonymous]

i do now have another problem

when i filter on period , the color of my title legend is changing

how can i fix this

[VahidDM]

Try to fix set the colour manually first, then test it with filters:

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

Appreciate your Kudos🙏

[Anonymous]

I did it but the problem is that the color of my legend tilte change , how can i fix it