Forum Discussion

zohra69's avatar
zohra69
Frequent Visitor
2 years ago
Solved

Dynamic Pie Chart Percentage

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
 

 

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    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's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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.

  • zohra69's avatar
    zohra69
    Frequent Visitor
    2 years ago

    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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.