Forum Discussion
Divide two columns only with values on dimension level en create KPI without dimension
Hallo,
I want to divide two values. In a tabel I can use filter to exclude the blank values but if I take out the dimension rows then the filter doesn't work any more. Here my data:
I want to divide columnA and columnB, but only if columA and columnB have values on the same row (per name):
In table I can use filter ...is not blank
But in KPI it just doesn't work:
Is there any solution in DAX for dit case? The KPI value shoud be 75%.
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.
4 Replies
- danextian
Super User
Hi 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
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.
