topic Re: Divide all attributes into specific attribute value in DAX Commands and Tips

Divide all attributes into specific attribute value

palchaw — Thu, 27 Jun 2024 20:22:35 GMT

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 how much of each attribute makes up the Total Assets. My current matrix looks like the below.

	12/31/2021	12/31/2022	12/31/2023
Entity 123
Cash	3	4	3
Inv	4	2	5
AR	5	7	7
PPE	5	5	6
Other	4	5	5
Total Assets	21	23	26

All data is in one table "Financials" with an Attribute column that contains the descriptions of the line items (Cash, Inv, AR, etc.) and a Value column that holds the respective value for each attribute. There's also an ID column in the Financials table as there are different entities' financial statements held in the same table.

The above table does not display data until a company in a separate matrix visual is clicked on.

My ideal output would look similar to the below.

Attribute	12/31/2021		12/31/2022		12/31/2023
Entity 123
Cash	3	14%	4	17%	3	12%
Inv	4	19%	2	9%	5	19%
AR	5	24%	7	30%	7	27%
PPE	5	24%	5	22%	6	23%
Other	4	19%	5	22%	5	19%
Total Assets	21	100%	23	100%	26	100%

I have tried the below measure to try to have the value for "Total Assets" as a denominator which I could then divide into, but this measure only shows the value for the "Total Assets" attribute row for each year, while every other attribute's row is blank.

__TotalBS =

CALCULATE(

SUM('Financials'[Value]),

'Financials'[Attribute] = "Total Assets")

Re: Divide all attributes into specific attribute value

vicky_ — Thu, 27 Jun 2024 23:06:30 GMT

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

Re: Divide all attributes into specific attribute value

palchaw — Fri, 28 Jun 2024 16:00:48 GMT

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!