Forum Discussion
Divide two columns only with values on dimension level en create KPI without dimension
Hi Jana2102
It is always important that when providing a sample data to describe where the numbers are from. Also, in the future please post a sample data that we can copy paste to Excel.
I modifed my formula a bit to point them to different tables but I am seeing 75%.
Please see the attached sample pbix.
Super UserHi Jana2102
Assuming that columns A and B have multiple rows for each Name and the numbers in your table are aggregates of these rows, try this:
Percentage =
-- Create a variable _TBL that filters summarized data
VAR _TBL =
FILTER (
-- Summarize the table by 'Name' and calculate sums for 'columnA' and 'columnB'
SUMMARIZECOLUMNS (
'Table'[Name], -- Group by 'Name'
"@colA", CALCULATE ( SUM ( 'Table'[columnA] ) ), -- Sum of 'columnA'
"@colB", CALCULATE ( SUM ( 'Table'[columnB] ) ) -- Sum of 'columnB'
),
-- Remove rows where either @colA or @colB is blank
NOT ( ISBLANK ( [@colA] ) ) && NOT ( ISBLANK ( [@colB] ) )
)
RETURN
-- Calculate the percentage as the sum of @colA divided by the sum of @colB
DIVIDE ( SUMX ( _TBL, [@colA] ), SUMX ( _TBL, [@colB] ) )
danextian thanks for your idea.
The columns are from 3 different tables:
- 'dim_Employee' [Name]
- 'fact_Hours_Registered' [colA]
- 'fact_Hours_Planned' [colB]
With your solution I don't get the result, what I'd like to see. I get 138,6% and not 75%.
Stil any idea?
- danextian
Hi Jana2102
It is always important that when providing a sample data to describe where the numbers are from. Also, in the future please post a sample data that we can copy paste to Excel.
I modifed my formula a bit to point them to different tables but I am seeing 75%.
Please see the attached sample pbix.