Dynamic Pie Chart Percentage

[zohra69]

Hi all

I have spend 1 days banging my head with this issue in trying to fix the Dax coding. I want to create a dynamic pie chart that change % segment based on various filters selected. 

the scenario is complex as follows

Tables and Columns to Be Used:

1. Fact Table:

   • Table Name: Fact_Astro_PU_ALL_ICB
   • Column to Divide: Actual Cost per 1000 ASTRO PU

2. "DatesCalendar" Table:

   • Columns:
     • Financial Year (Filter)
     • Financial Quarter (Filter)

3. "Dim_Region" Table:

   • Columns:
     • RegShort (Filter) 
     • ICB (Sub-entity granular details of RegShort (each region has a number of ICB sub-entities linked to it)

Here my Dax measurement 

DynamicSegmentPercentage =

VAR TotalCost =

    CALCULATE(

        SUM('Fact_Astro_PU_ALL_ICB'[Actual Cost per 1000 ASTRO PU]),

        ALL('DIM_Region'[RegShort], 'DIM_Region'[ICB]),

        'DatesCalendar'[Financial Year] = IF(ISFILTERED('DatesCalendar'[Financial Year]), SELECTEDVALUE('DatesCalendar'[Financial Year]), 'DatesCalendar'[Financial Year]),

        'DatesCalendar'[Financial Quarter] = IF(ISFILTERED('DatesCalendar'[Financial Quarter]), SELECTEDVALUE('DatesCalendar'[Financial Quarter]), 'DatesCalendar'[Financial Quarter]),

        'DIM_Region'[RegShort] = IF(ISFILTERED('DIM_Region'[RegShort]), SELECTEDVALUE('DIM_Region'[RegShort]), 'DIM_Region'[RegShort])

    )

VAR SelectedCost =

    CALCULATE(

        SUM('Fact_Astro_PU_ALL_ICB'[Actual Cost per 1000 ASTRO PU]),

        ALL('DatesCalendar'),

        ALLEXCEPT('DIM_Region', 'DIM_Region'[RegShort]),

        'DatesCalendar'[Financial Year] = IF(ISFILTERED('DatesCalendar'[Financial Year]), SELECTEDVALUE('DatesCalendar'[Financial Year]), 'DatesCalendar'[Financial Year]),

        'DatesCalendar'[Financial Quarter] = IF(ISFILTERED('DatesCalendar'[Financial Quarter]), SELECTEDVALUE('DatesCalendar'[Financial Quarter]), 'DatesCalendar'[Financial Quarter]),

        'DIM_Region'[RegShort] = IF(ISFILTERED('DIM_Region'[RegShort]), SELECTEDVALUE('DIM_Region'[RegShort]), 'DIM_Region'[RegShort]),

        'DIM_Region'[ICB] = IF(ISFILTERED('DIM_Region'[ICB]), SELECTEDVALUE('DIM_Region'[ICB]), 'DIM_Region'[ICB])

    )

RETURN

    DIVIDE(SelectedCost, TotalCost

Issue, when i filter by Financial year and or financial Quarter and by region %pie chart segment does not tally back to 1 or 100% and has inaccurate % for selected region?

and if i drill down to SW region, the pie chart does not segment by ICB Sub entities and shows inaccurate %

 If someone can pinpoint my error much appreciated

 

[lbendlin · Accepted solution]

If someone can pinpoint my error 

You are using a pie chart.

In the vast majority of cases a pie chart is the wrong visual type.  It is only applicable if no filters are applied, or if the data in that pie chart truly represents 100% of the data subject.

Consider trying out other chart types.

[Anonymous · Accepted solution]

Hi @zohra69 ,

As @lbendlin  said, pie charts work best when showing a limited number of categories to compare parts of a whole.
But you can try modifying the expression, something like

VAR TotalCost =
CALCULATE(
SUM('Fact_Astro_PU_ALL_ICB'[Actual Cost per 1000 ASTRO PU]),
ALLSELECTED('DatesCalendar'),
ALLSELECTED('DIM_Region')
)

The ALLSELECTED function removes contextual filters from columns and rows in the current query, while retaining any other contextual filters or explicit filters. For detailed information, please refer to the document: ALLSELECTED function (DAX) - DAX | Microsoft Learn.

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.

[Ashish_Mathur]

Hi,

Share a condensed version of the file with only those tables which are required.  Clearly show the problem there.

[zohra69]

thank you for your prompt response. the pie chart  represents 100% data as the information derived from same table. Only difference is that my dates are from a different DatesCalendar table. 

[lbendlin · Accepted solution]

If someone can pinpoint my error 

You are using a pie chart.

In the vast majority of cases a pie chart is the wrong visual type.  It is only applicable if no filters are applied, or if the data in that pie chart truly represents 100% of the data subject.

Consider trying out other chart types.