Forum Discussion

DonapatiSP_3105's avatar
DonapatiSP_3105
New Member
4 years ago
Solved

Total Label Value into variable - Stacked bar chart

In a stacked bar chart, i want to show percentage of each stack in a bar. I have tried using below. This worked well for many other reports. In a particular report, i get only 100%. I have tried all combinations to achieve it.

 

% Amended = calculate(count(Total_Amended[RegistrationNumber]))/CALCULATE(COUNT(Total_Amended[RegistrationNumber]),ALLSELECTED(Total_Amended[Status]))

 


 

I want to get a value of total of the bar into a variable. Is it possible?
Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi DonapatiSP_3105 ,

     

    Can you share your .pbix file for test?

    Remember to remove the confidential information.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi DonapatiSP_3105 ,

     

    Try:

    % Amended =
DIVIDE (
    CALCULATE (
        COUNT ( Total_Amended[RegistrationNumber] ),
        FILTER (
            ALLSELECTED ( Total_Amended ),
            Total_Amended[New_Status] = MAX ( Total_Amended[New_Status] )
                && Total_Amended[RegionName] = MAX ( Total_Amended[RegionName] )
        )
    ),
    CALCULATE (
        COUNT ( Total_Amended[RegistrationNumber] ),
        FILTER ( ALLSELECTED ( Total_Amended ), [RegionName] = MAX ( [RegionName] ) )
    )
)

    I made a simple sample for reference,you will see below in my example:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my areply as a solution!

     

5 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi DonapatiSP_3105 ,

     

    Try:

    % Amended =
DIVIDE (
    CALCULATE (
        COUNT ( Total_Amended[RegistrationNumber] ),
        FILTER (
            ALLSELECTED ( Total_Amended ),
            Total_Amended[New_Status] = MAX ( Total_Amended[New_Status] )
                && Total_Amended[RegionName] = MAX ( Total_Amended[RegionName] )
        )
    ),
    CALCULATE (
        COUNT ( Total_Amended[RegistrationNumber] ),
        FILTER ( ALLSELECTED ( Total_Amended ), [RegionName] = MAX ( [RegionName] ) )
    )
)

    I made a simple sample for reference,you will see below in my example:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my areply as a solution!

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    FYI that you dont need the first CALCULATE in that expression.  Also, your popup shows a "New_Status" field but your expression removed the filter from the "Status" column.  Did you just rename for visual?  If not, you need to use the legend column in your ALLSELECTED.

     

    Pat

     

    • DonapatiSP_3105's avatar
      DonapatiSP_3105
      New Member
      4 years ago

      Hey Pat,

       

      Thanks for your response. Yes, calculate is not need in the numerator. I had removed filter which was applied and didnt remove calculate.

       

      Yes, "New_Status" field is the right filter. I had renamed it for the visual.  I had first tried with legend column in ALLSELECTED, i was getting 100% across.

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        4 years ago

        Hi DonapatiSP_3105 ,

         

        Can you share your .pbix file for test?

        Remember to remove the confidential information.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!

  • DonapatiSP_3105's avatar
    DonapatiSP_3105
    New Member
    4 years ago

    I was getting 100% always because the given denominator used for calculating percentage are coming from different table using relationship. once that is corrected, the values were coming as expected.