Forum Discussion
Using Measures in Another Measure
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
Highlight your measure in the field list and then use the Modelling tab in Power BI Desktop to change.
13 Replies
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.
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
- Can you set the decimal point to show multiple places
Hi,
Please ensure that all those formulas are measures and not calculated columns.
Hi,
Try this measure
=CALCULATE(SUM(F_SALES[COUNT]),D_SALES[PERSON_TYPE] = "Internal")/SUM(F_SALES[COUNT])
Does this work?
