Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
I have a table containing sales units (sales data[sales units]), I would like to create another column which shows the percentage % of each sale against the total sales.
I'm not sure whether I need to create a measure for total sales and then divide the (sales data[sales units]) by the measure.......but I could be completely wrong.
Any help/guidance would be greatfully appreciated.
Regards
Md
Solved! Go to Solution.
Okay I had to add an Index Column in the Query Editor to the table
(Query Editor => Add Column tab => Index Column => From1 => Close & Apply)
And then use this Index to create another COLUMN
Orignal Data COLUMN = VAR ThisPeriod = 'Table'[Index] RETURN 'Table'[Cumulative Column] - CALCULATE ( SUM ( 'Table'[Cumulative Column] ), FILTER ( 'Table', 'Table'[Index] = ThisPeriod - 1 ) )
Here's the result...
Good Luck!
here's the COLUMN formula that will calculate this
% of Total Units COLUMN = DIVIDE ( 'Sales Data'[Sales Units], SUM ( 'Sales Data'[Sales Units] ), 0 )
and the MEASURE
% of Total Units MEASURE = DIVIDE ( SUM ( 'Sales Data'[Sales Units] ), CALCULATE ( SUM ( 'Sales Data'[Sales Units] ), ALLSELECTED ( 'Sales Data' ) ), 0 )
Hope this helps!
That worked great - thank you.
My next xhallenge is to take a column with a cumulative set of values, and break it down into their non-cumulative values........any ideas?
Regards
Md
Okay I had to add an Index Column in the Query Editor to the table
(Query Editor => Add Column tab => Index Column => From1 => Close & Apply)
And then use this Index to create another COLUMN
Orignal Data COLUMN = VAR ThisPeriod = 'Table'[Index] RETURN 'Table'[Cumulative Column] - CALCULATE ( SUM ( 'Table'[Cumulative Column] ), FILTER ( 'Table', 'Table'[Index] = ThisPeriod - 1 ) )
Here's the result...
Good Luck!
Fantastic - worked great.
Can you please explain in laymans terms what the index and subsuquest formula does?
Regards
Md
Okay here's an example with a Date Column instead of an Index
(because most likely you have a Date Column and its easier to understand)
Orignal Data COLUMN = VAR CurrentDate = 'TableName'[Date] VAR PreviousDate = CALCULATE ( MAX ( TableName[Date] ), FILTER ( 'TableName', TableName[Date] < CurrentDate ) ) RETURN TableName[Running Total] - CALCULATE ( SUM ( TableName[Running Total] ), FILTER ( 'TableName', 'TableName'[Date] = PreviousDate ) )
Here's the result
DAX works on Tables and Columns NOT on Cells (like Excel)
so you need Context and that's why you need an Index or the Date Column!
Hope this makes sense!
Good Luck!
Thanks Sean
Hugely appreciated.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |