Forum Discussion

dejanzoric's avatar
dejanzoric
Frequent Visitor
2 years ago
Solved

Ratio calculation from same column

The below screenshot is an example of sorted data where we have ID (LN_TAG) and value. I don't know how to make a percentage.

The percentage represents the relationship:
01_Real estate/04_Total without duplicating values
02_Insurance policy/04_Total without duplicating values
03_Real estate valuation/04_Total without duplicating values
04_Total without duplicating values/04_Total without duplicating values

I am unable to do so in Power BI, please help.

General Comment
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    according to DAXFormatter.com that code is ok

     

    M =
IF (
    HASONEVALUE ( 'Sheet1'[CATEGORY] ),
    DIVIDE (
        SUM ( 'Sheet1'[SUM_VECA_S4] ),
        CALCULATE (
            SUM ( 'Sheet1'[SUM_VECA_S4] ),
            'Sheet1'[CATEGORY] = "04_Total without duplicating value"
        ),
        0
    )
)

    do you still get the error message?

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
    If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • dejanzoric's avatar
      dejanzoric
      Frequent Visitor
      2 years ago

      Hi, first of all thank you for wanting to help me.

      I am attaching a data table

       

      CATEGORYSUM%
      01_Real estate19.010.195 
      02_Insurance policy40.228.138 
      03_Real estate valuation6.820.222 
      04_Total without duplicating value   59.174.339 

       

      I want to calculate the percentage of participation in the total in the table
       
      The percentage represents the relationship:
      01_Real estate/04_Total without duplicating values
      02_Insurance policy/04_Total without duplicating values
      03_Real estate valuation/04_Total without duplicating values
      04_Total without duplicating values/04_Total without duplicating values
       
      Thanks in advance,
      Dejan

       

       

       

       

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Be aware that in standard scenarios you can add the sum again and then

        change the "show value as" to % of Grand Total

        Which gives

         

        In your scenario you need a measure to say

         

        Measure = divide(sum('Table (3)'[SUM]),CALCULATE(sum('Table (3)'[SUM]),'Table (3)'[CATEGORY]="04_Total without duplicating value"),0)

         

         

         

        Probably want to suppress the Total value:

         

        Measure = if(hasonevalue('Table (3)'[CATEGORY]),divide(sum('Table (3)'[SUM]),CALCULATE(sum('Table (3)'[SUM]),'Table (3)'[CATEGORY]="04_Total without duplicating value"),0))