Using Measures in Another Measure

[rajiv_tarafdar]

Hi Guys, 

I need to calculate a measure from the selected values of the same Measure. 

I need to calculate what portion of sales people are internal of the total sales count i.e. Inter Sales Count is 15% of Total Sales Count. 

- Scenario - 

Fact_sales.Count has all the count for sales person (Existing Measure)

D_Sale.Sales_Type has the type of Sales Person (Existing Attribute)

I need a formula to calculate the following

% Internal = 

SUM(CASE D_Sale.Sales_Type WHEN "Internal" THEN FACT_SALES.COUNT ELSE 0 END )/ 

SUM(FACT_SALES.COUNT)

I am struggling to get the DAX for this. I tried this but it does not show the right value

Internal Count = CALCULATE(SUM(F_SALES[COUNT]), D_SALES[PERSON_TYPE] = "Internal")

-- This works and shows correct value

% Internal = CALCULATE(F_SALES[Internal Count] / SUM(F_SALES[COUNT])) 

-- This does not work. It shows some crazy numbers

Can someone please help? 

TIA

[Ashish_Mathur]

Hi,

Please ensure that all those formulas are measures and not calculated columns.

[rajiv_tarafdar]

Hi @Ashish_Mathur

INTERNAL and %INTERNAL are measures. Rest are columns direct from the data source. 

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(SUM(F_SALES[COUNT]),D_SALES[PERSON_TYPE] = "Internal")/SUM(F_SALES[COUNT])

Does this work?

[rajiv_tarafdar]

Nope, same results 0 / 1

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[rajiv_tarafdar]

Sorry @Ashish_Mathur I cant share the file as its too large (650MB+) and client data. 

Here is a sanpshot of the outcome and formula

[Phil_Seamark]

Hi @rajiv_tarafdar

Please use the following syntax and make sure you format the measure uses the decimal data type and at least 4 decimal points

WEIGHTING = 
    DIVIDE (
        CALCUATE(SUM(F_P......) ,
        SUMIF(F_PUBLI.....) ,
        0
       )

[Phil_Seamark]

HI @rajiv_tarafdar

For your last measure [% Internal], you don't need a CALCULATE.

For diagnostic purposes, create a new measure that does the following :

test measure =  SUM(F_SALES[COUNT])

And see if this gives you the number you are expecting.

You already mentioned the [Internal Count] measure is ok.

[rajiv_tarafdar]

HI @Phil_Seamark

I tried without the CALCULATE function too and that did not work. I get a value of 1 / 0 for aggregated columns. 

E.g. SALES COUNT = 643

INTERNAL COUNT = 287

%Internal = 0

[Phil_Seamark]

Can you set the decimal point to show multiple places

[rajiv_tarafdar]

hi @Phil_Seamark I cant see an option to set data types or decimal places for measures. How do I do it?  

[Phil_Seamark · Accepted solution]

Highlight your measure in the field list and then use the Modelling tab in Power BI Desktop to change.

[rajiv_tarafdar]

Thanks @Phil_Seamark That seems to give me some data. Thanks. I am looking into the calculation.