Forum Discussion

palchaw's avatar
palchaw
Frequent Visitor
2 years ago
Solved

Divide all attributes into specific attribute value

Hello,   I am creating a balance sheet financial statement output that shows line items with their actual values for each of the past three years. I need to add in a percentage column that shows ho...
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    To fix your measure (keeping the same dataset), i would do something like:

    __TotalBS =
CALCULATE(
    SUM('Financials'[Value]),
        ALL('Financials'[Attribute]),
        'Financials'[Attribute] = "Total Assets"
        )

    Is there any reason you have total assets as an attribute? if you get rid of the total assets attribute, and your data looks like:

    then you can just remove that last line:

    __TotalBS =
CALCULATE(
    SUM('Financials'[Value]),
        ALL('Financials'[Attribute])
        )

     

vicky_'s avatar
vicky_
Super User
2 years ago

To fix your measure (keeping the same dataset), i would do something like:

__TotalBS =
CALCULATE(
    SUM('Financials'[Value]),
        ALL('Financials'[Attribute]),
        'Financials'[Attribute] = "Total Assets"
        )

Is there any reason you have total assets as an attribute? if you get rid of the total assets attribute, and your data looks like:

then you can just remove that last line:

__TotalBS =
CALCULATE(
    SUM('Financials'[Value]),
        ALL('Financials'[Attribute])
        )

 

palchaw's avatar
palchaw
Frequent Visitor
2 years ago

This measure helped, thank you.

At first, it was still coming up with the blank error where the measure only populated a value for "Total Assets" when I tried this on my live data that looks similar to the example data in my forum question. So I recreated the example data in my file and your measure worked perfectly. I ended up building my live table from scratch and found that when I did a custom sort on Attribute by a different column "Sort ID", that's when the measure went blank. I found a link (https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-use-the-all-function/) that explained the "Sort ID" column would have to be added into the ALL() function in order for the measure to populate correctly for all attributes.

Thanks!