Context - Dynamic Segmentation

rit372002 · June 16, 2021, 4:57pm

Hi experts,

https://drive.google.com/file/d/1wPvl8XlgPHIY7LY3bnd0aEQ0_hieCKaf/view?usp=sharing

I am trying to understand the context piece. I always have difficulty getting my head wrapped around it. Here is the situation:

I have a parent_customer - ‘Cooperative Farmers Elevator’.

It has bunch of growers - ‘rpt_grp_key_unique’.

They buy different products which has catalyst points, shown in “With Products” tab. So I have got 20.3 million worth of sales amount and 1.5 million worth of catalyst points.

My quesiton is: I only want to have growers who have bought more than 2500 catalyst points worth of products.

I created “Growers Over 2500” tab based off of performance analyser to show which numbers I should see. In this situation, 15.1 million for sales amount and 1.1 million for catalyst points for only those growers who have more than 2500 points…

But I want to show this on “With Products” tab.

So long story short, I want to see 15.1 million and 1.15 million instead of 20.3 and 1.5 respectively on “With products” table.

I have put the sample pbix file on google drive.

Thanks in advance,

-RK

deltaselect · June 18, 2021, 11:41pm

Hi @rit372002

My provided solution apparently did not work: individual lines from both reports do not add up to the totals, which are correct, the measures do not work for too detailed granularity per grower and how to calculate the part of catalyst points per product-line where growers qualify ?

Catalyst_Community - WIPv4.pbix (53.6 MB) Left the Adjustment of the data model

Back to zero: one calculated table with catalyst points and sales per grower per product ?
I will try to come with the solution. If others jump in, happy to hear .

Kind regards, Jan van der Wind

EnterpriseDNA · June 21, 2021, 1:00am

Hi @rit372002, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

rit372002 · June 21, 2021, 7:24pm

@deltaselect Thanks for all the hard work. I came up to the solution. Let me know if there are holes in the solution or if it can be made better. Here are the steps:

I created “Unique rpt” table.

Made “Grower Over 2500” as disconnected table and created “Unique rpt” table to get just the parent customer number and growers which had more than 2500 points. Joined it with “With Products” table. This is a single join coming from “Unique rpt” so I should get all the growers who have more than 2500 points. Others will be blank.

Between “With Products” and “Fee Reduction” created join like this. But I think I can get away with “Both” as well.

Now looked for all the growers except the blank ones.

Here is the pbix with solution tab.

https://drive.google.com/file/d/1CZCgdSvhMpARC-sEGzZHBd2vz4g_ZLxi/view?usp=sharing

Thanks,
-RK

Keith · June 21, 2021, 10:12pm

i think you might have a model data problem as you had many to many relationship which isn’t good. I’m not sure other expert has seen that

deltaselect · June 22, 2021, 12:29am

Hi @rit372002
I can follow that you have to create a calculated table “With Products” and “Unique Rpt” to create the product report. Unfortunately you have to create a table " Grower over 2500".
You have your results, that is what matters !
Nice table programming. Just curious, what does TopN do within the calculated table DAX formula ?

I try to work out a similar, but slightly different approach, based upon a calculated table with Catalyst Points and Sales from “rpt_grp_key_unique” in combination with “Product category key”, and
looking to use previous measures, with TREATAS function, based upon the new table results, see if that will work.

Kind regards, JW

rit372002 · June 22, 2021, 1:09am

@deltaselect Thanks! Yes, I was hoping to do this with just measures and maybe it’s possible but my mind is fried to even get to this solution. Maybe other experts know how to do it more efficiently.

BTW, The TOPN part is redundant. I could have used __DS0Core variable to return results. I use shortcut of “Performance Analyzer” to created my calculated tables which creates The TOPN piece of code. I think it does that because to show data on 1 page. But I think totally redundant in paginated report situation and for all practical purposes.

Thanks,
-RK

deltaselect · June 23, 2021, 7:19pm

Hi @rit372002

This might interest you:
it is possible to build the Products report with one calculated table, when adding a Summarize formula in your calculated table “With Products” , see calculated table “With Products 2” and report “Solution2 With Products” . which is added to your provided Solution , see
Catalyst_Community - WIP - Solution R II.pbix (57.0 MB)

var QualifyingCatPointsProgram = 
      FILTER(  SUMMARIZECOLUMNS(
               'Grower Master'[rpt_grp_key_unique],
                "CatPoints", [Total Catalyst Points]),
        [CatPoints] >= 2500)

Together with your filters in the calculated table “With Products 2” , the report can be build without the tables “Unique rpt” and “Growers Over 2500”, and without connections in the data model .
When using the same methodology, it is possible to build the other report as well, see “Growers Over 2500 2”, without the use of the table Growers 2500, and without further connections in your data model.

==========================
I tried again the earlier provided method with measures and the simplified data model.
For the report Growers > 2500 it now does work, previously I used the wrong key, when using “rpt_grp_key_unique” , it gives the correct figures with the measures. ( I added “rpt_grp_key_unique” to the Grower Master lookup table.) See measures Group “Grower2500 Measures”

Catalyst_Community - WIPv8.pbix (56.7 MB)

For the Product reporting a calculated table is needed to combine products and programs, to determine the part of the product sales belonging to qualifying programs.
Created is a generic calculated table, “Combi4 Growers Products”, not linked in the data model, which can be used, with TreatAs measures, together with the information from the lookup tables to build the required “With Products” report, see measure group “Combi Product measures”
However (!) the number does not exactly match the required numbers, not clear for me why not, and when selecting all the 6 columns from different lookup tables (like Description) the report becomes (very) slow. With more knowledge about the data model and data tables, the correct figures probably can be made.

Thank you for using the forum.
If you have further remarks or questions, please let me know.
Kind regards, JW

EnterpriseDNA · June 25, 2021, 1:01am

Hi @rit372002 , did the response provided by @deltaselect help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

EnterpriseDNA · June 29, 2021, 1:25am

Hi @rit372002, we’ve noticed that no response has been received from you since the 22nd of June.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · June 30, 2021, 12:47am

Hi @rit372002, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.