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_Tot...
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?

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))

     

     

     

     

    • dejanzoric's avatar
      dejanzoric
      Frequent Visitor
      2 years ago

      Thanks, lbendlin In fact, I want to achieve it as shown in the last table.

      I entered the formula as given in the example but it returns the following error:

      "Too many arguments were passed to the SUM function. The maximum argument count for the function is 1".

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        please show your measure code

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share the raw data, not the visual.

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, dejanzoric 

    try these one 

    % =
    var num = SUM('Table'[SUM])
    var deno = CALCULATE(SUM('Table'[SUM]),'Table'[CATEGORY]="04_Total without duplicating value ")
    return
    DIVIDE(num,deno)
     
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.