Calcualte 100% stacked of each column in stacked visualization

[Anonymous]

Hello Power BI community

I Need to build a measure that can calculate 100% based on all categories for each month. I want to apply this measure to my visualized Stacked column Chart. (I know I can use a 100 % Stacked column chart to show the correct percentage) But I only want to show this measure in my tooltips and keep the original visualization.

This is my Dax code so far:

Percentage = DIVIDE([Total cases],CALCULATE([Total cases],ALLEXCEPT(SALESFORCE_CASES_FCT_20210429, SALESFORCE_CASES_FCT_20210429[CASE_ORIGIN (groups)],DIM_DATE[YYYY-MM])),0)

Just like in the example in the last picture. I want to find the measure with "51,97%" and apply it to the tooltips in the first visualization .

Thanks for help

Best Regards

Khanh


Original visualizaitongroup based of a column

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can update the formula of measure [Percentage] as below:

Note: please update the name of date field with the correct one in the table SALESFORCE_CASES_FCT_20210429 .

Percentage =
DIVIDE (
    [Total cases],
    CALCULATE (
        [Total cases],
        FILTER (
            ALLSELECTED ( 'SALESFORCE_CASES_FCT_20210429' ),
            FORMAT ( 'SALESFORCE_CASES_FCT_20210429'[Date], "YYYY-MM" )
                = SELECTEDVALUE ( 'DIM_DATE'[YYYY-MM] )
        )
    ),
    0
)

Best Regards

[amitchandak]

@Anonymous , I think it should be the only case Origin in all except

Percentage = DIVIDE([Total cases],CALCULATE([Total cases],ALLEXCEPT(SALESFORCE_CASES_FCT_20210429, SALESFORCE_CASES_FCT_20210429[CASE_ORIGIN (groups)])),0)

[Anonymous]

Hello amitchandak thanks for the answer.

However, I still experienced the issue.  Example, Its give me percentage of 4,41 % when it should be 40,74 %

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can update the formula of measure [Percentage] as below:

Note: please update the name of date field with the correct one in the table SALESFORCE_CASES_FCT_20210429 .

Percentage =
DIVIDE (
    [Total cases],
    CALCULATE (
        [Total cases],
        FILTER (
            ALLSELECTED ( 'SALESFORCE_CASES_FCT_20210429' ),
            FORMAT ( 'SALESFORCE_CASES_FCT_20210429'[Date], "YYYY-MM" )
                = SELECTEDVALUE ( 'DIM_DATE'[YYYY-MM] )
        )
    ),
    0
)

Best Regards

[Anonymous · Accepted solution]

Hi @_Rena
thanks you for your help.

I don't know why but Its also works when I tried with All function.

Percentage cases = DIVIDE([Totalcases],CALCULATE([Totalcases],ALL(SALESFORCE_CASES_FCT_20210429[Case_origin group])),0)

Best regards 
Khanh