Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a matrix with Region, Report as the dimension and the expression is given below;
Test Value =
IF(
(SELECTEDVALUE(POC[Report]) = "Gross Profit in % of Sales"),
(CALCULATE(SUM(POC[Value ($)]), FILTER(POC, POC[Report] = " Gross Profit"))
/
CALCULATE(SUM(POC[Value ($)]), FILTER(POC, POC[Report] = " Sales"))) ,
Sum(POC[Value ($)])
)
Logic: For all the Report Parameters the Values = Sum(Values) except for Gross Profit in % of Sales.
For Gross Profit in % of Sales, the value is Sum(Gross Profit)/Sum(Sales)
But the Report Parameter = "Gross Profit in % of Sales" is filtering out from my table as shown below;
What wrong am I doing here?
any help is appreciated
thanks
Bhavesh
Hi @Bhaveshp
As tested with my example data, some parts of the formula are wrong to result as you expected.
SELECTEDVALUE = IF(SELECTEDVALUE(Sheet4[report])="Gross Profit in % of Sales",1,0)
Gross Profit = CALCULATE(SUM(Sheet4[value]), FILTER(Sheet4,Sheet4[report]= "Gross Profit"))
Sales = CALCULATE(SUM(Sheet4[value]), FILTER(Sheet4,Sheet4[report]= "Sales"))
g/s = [Gross Profit]/[Sales]
I can't find Report Parameter = "Gross Profit in % of Sales" in the table.
I'm confused by the this sentence:
But the Report Parameter = "Gross Profit in % of Sales" is filtering out from my table
And what you'd like to see in the chart, could you kindly give me an example of dataset and result visual which you could create manualy in the excel to give me some refernce?
Best Regards
Maggie
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |