Count of positive values in sub query
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|
|sub total 4|
|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 -
DISTINCTCOUNT('FACT_TABLE'[Ship To ID]), -- it counts count of ship to
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?
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.
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?
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.
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?
Hi Ian Macdonald ,
Can you please let me know if my question is still in progress? Would appreciate if you talk me through it.
Yes, I will reply. Been busy on other things. certainly by next Monday.
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.