Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Highlight your measure in the field list and then use the Modelling tab in Power BI Desktop to change.
Hi,
Please ensure that all those formulas are measures and not calculated columns.
INTERNAL and %INTERNAL are measures. Rest are columns direct from the data source.
Hi,
Try this measure
=CALCULATE(SUM(F_SALES[COUNT]),D_SALES[PERSON_TYPE] = "Internal")/SUM(F_SALES[COUNT])
Does this work?
Nope, same results 0 / 1
Hi,
Share the link from where i can download your PBI file.
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
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 )
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.
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
hi @Phil_Seamark I cant see an option to set data types or decimal places for measures. How do I do it?
Highlight your measure in the field list and then use the Modelling tab in Power BI Desktop to change.
Thanks @Phil_Seamark That seems to give me some data. Thanks. I am looking into the calculation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |