Count of positive values in sub query
hi all,
Can you please help me to reproduce the metric from PBI DAX calculation in PQL?
the story is the following - at any time dynamically we need to have counted positive values within internal grouping.
as an example how it works -
how count of # profit was calculted -
there is another dimention in the model called "ship to ID" and if we add that in the counting it appears as :
| category | person | ship to ID | sum of profit | count of # positive profit by ship to |
| books | Cansu O | 1 | 10 | 1 |
| books | Cansu O | 2 | 5 | 1 |
| books | Cansu O | 3 | 7 | 1 |
| books | Cansu O | 4 | 8 | 1 |
| books | Cansu O | 5 | -10 | 0 |
| sub total 4 | ||||
| pens | Cansu O | 1 | -15 | 0 |
| pens | Cansu O | 2 | 28 | 1 |
| pens | Cansu O | 3 | 15 | 1 |
| pens | Cansu O | 4 | -15 | 0 |
| pens | Cansu O | 5 | -28 | 0 |
| pens | Cansu O | 6 | -7 | 0 |
| sub total 2 |
summing up, 'count of # positive profit by ship to' takes into account split by ship to id, and it works dynamically, if users create another table with different dimentions count of ship to will be dynamically counted.
when we do that in DAX we can use a kind of sub-query with SUMMARIZE option, then to prefilter >0 values and then to count distinct ship to ids -
CALCULATE(
DISTINCTCOUNT('FACT_TABLE'[Ship To ID]), -- it counts count of ship to
FILTER(
SUMMARIZE(FACT_TABLE,FACT_TABLE[Ship To ID],"calc profit", SUM('FACT_TABLE[profit])), -- it groups fact table by SHIP TO ID and calcs total
[calc profit]>0)) -- it filters >0 ship to IDs by cal profit
can you please help to find the best solution in pyramid analytics in formulate to reproduce the same logic?
Many thanks.
BR, Val
10 replies
-
Hi Valeriia,
Can you please tell me what version of Pyramid you are using? That determines the approach that needs to be taken to solve this problem.
Thanks,
Ian
-
Hi Val,
Sorry, some more questions.
Is this example table the base, row level data? At what level of aggregation is the testing of Sum of Profit if it is +/- ? Is Sum of Profit grouped always by Category and Person and Ship To Id to determine its sign?
Thanks,
Ian
-
Ian Macdonald
Hi Ian,
thank you for your support.let's imagine that it's row level data. Then we have some users' scenarios -
1) user groups table by category+person.
In our calculation we add the third level as ship to ID, calculate +/- and then return back values.
(it's presented in the topic)2) user groups table by category.
In our calculation we add the second level as ship to ID, calculate +/- and then return back values.
the result will look the same as in 1st, because in my example we have only 1 person within 1 category. (it's presented in the topic)
But in general it works as described in 3rd scenario.3) user groups table by person.
In our calculation we add the second level as ship to ID, calculate +/- and then return back values.users add person and 2 measures.
let's figure out what we have to show. add ship to id as next level and calculte +/-
the result is:
in other words, we need to use ship to ID dimention as an additional dimention next to the users' choice ( we do make that like in hidden sub-query).
I hope it helps to catch what I'm looking for.VR,
Val
-
-
Hi Valeriia,
In that case it is a vey simple calculation, but using the new feature in Pyramid2023, "Custom Columns". I'm assuming you are working in Direct Query mode, as otherwise you can easily solve this in the Pyramid Data Flow.
All we need to do is to examine each row of the table and create a calculation where if Profit is positive, assign a value of 1 otherwise a value of 0. Set this to be Aggregate aggregation in the model and it will naturally gives the correct count of +Profit by any dimension. You can calculate this from Discover and share the calculation with others, but in this instance it is probably best defined as a virtual column on your model.
I've taken you data set and created a small Pyramid model.
On the Columns section add a new calculation:
Create the following calculation against the Profit column. Create the IF statement, using the Model Attribute block to specify the Profit column. Click on Preview button to test the calculaiton. you can see the results in the Results Preview panel. Give the column a name and hit apply.
Set the column to be a Measure, with SUM aggregation and display as a number with no decimal places.
Hit the Pyramid icon to process the model.
You can then use Positive Profit Count in any scenario:
This is a nice, simple and elegant solution in this specific scenario. Where there may be more than two entries in the data for the combination of category, person and ship to id, it becomes significantly more complex. LMK if we need to go there.
Hope that helps.
Ian
-
Hi Ian,
many thanks for your explanations - it's nice to know about this feature! this case works as expected.
can we try to figure out more complex question - if we have repeated values, I mean we have another dimension diving us to repeated values where we cannot use this approach.just come up with an idea - data is split additionally by date, where for 1 day we have both positive and negative values summing up as negative value in this date.
our current approach calculate then as 1. And it's not correct. can we tweak that?
BR,
Val -
Hi Ian Macdonald ,
Can you please let me know if my question is still in progress? Would appreciate if you talk me through it.
Thanks.
BR,
Val -
Hi Valeriia,
Yes, I will reply. Been busy on other things. certainly by next Monday.
Ian
-
Hi valeriia ,
Using Ian's same model, you can create this custom measure:
if([Sheet1].[ship to ID].currentmember.uniquename != "*", // ship_to_id is in the query if([measures].[Sheet1 Profit]>0,1,0), // is the datapoint positive sum([Sheet1].[ship to ID].allmembers, if([measures].[Sheet1 Profit]>0,1,0)) // count the ship_to_id that are positive )This should work dynamically in any context of columns that you use in discovery.
No columns, only Category, Category + Person, Category + Person + Ship To...
And should work the same if you have more granular columns such as date.