Divide two columns only with values on dimension level en create KPI without dimension

[Jana2102]

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%.

[Jana2102]

Great work @danextian. This helps me futher. I get the results what I need.

Thank you very much.

[danextian]

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

[Jana2102]

@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 · Accepted solution]

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.