I need some help calculating variables at different levels of detail please.
I have a report - see attached - where the user can select different Cases and within those cases, different customers, for a range of dates.
The report then shows the stock levels they hold against required percentage limits and decides whether the levels are within or exceed the %.
There are two main variable SV - stock value and TPV - Total Value.
There are two visualisations that are relevant. The SUMMARY shows the Outcome by Case ID/ Customer Code for all dates (the effect of the date has been removed from this visual) and the DETAIL shows detailed information by Case ID/ Customer Code for the date selected including the Outcome.
The amounts of Total SV and Total TPV change depending on the Customer Code and the dates selected in the slicers.
Everything in the detail table is working fine. The issue I have is that the SUMMARY table is not working the way I want it to. If I selected Customer Code C1 and Valuation Date 2021-12-31, then all the Outcomes in the DETAIL table show as “SV Exceeds Allowed %, TPV Within %”, which is highlighted in green. The summary table should the data for that date as the same, i.e. “SV Exceeds Allowed %, TPV Within %” but the result is showing as “Both SV and TPV exceed allowed %”.
Evidently something is wrong in my calculations. I think I should be using sumx and averageX and maybe a summarise or a group by but I can’t get my head around the combinations.
I can’t store this in the table as it is completely dynamic as to the combination of Customer IDs that could be chosen on a case. There can be up to 20 customers per case and many more stocks.
Is what I am trying to do possible?? I really appreciate any help you can offer.
Hi @sgibbons083 - Have gone through the Report, primarily context is different in Summary and Detailed page due to additional fields in detail page, hence output is different.
If you add your base measures in the Summary page you can check that based on the “Outcome” condition result is correct.
It is not possible to get the same results in Detailed and Summary page as the outcome is a text value and you can’t sum/Average a Text function.
What you can do is create a measure using Summarize function as below to get the data similar to what you have in detailed, but need to define a condition to get the final result based on Min/Max or Counts etc.
Hi Ankit,
The idea is to get the “worst” outcome to show.
There is a hierarchy of outcomes that are ranked best to worst.
For e.g. if one of the Customer Codes within the Case has “Within Limits” and another one has “Both SV and TPV exceed allowed %” (which is considered worse) then the Outcome should show “Both SV and TPV exceed allowed %”.
I am finding it hard to use Max or Min or something because my Outcome is currently based on a measure. I had to do it this way because of the dynamic nature of the choice of the Cases and customers and needing to roll up the Outcomes based on the selected values.
I am sure there is a solution, I just haven’t found it yet.
Thanks
Sarah
Hi @sgibbons083 - Try below measure. In “SortVal” variable, add all the conditions that are needed ensuring no two Outcome return same Sort_Val value.
Lowest_Outcome = var base = SUMMARIZE(Sheet1,Sheet1[Case ID],Sheet1[Valuation Date],Sheet1[Classification],Sheet1[Sub Classification],Sheet1[Code],"Outcome_var",[Outcome])
var SortVal = ADDCOLUMNS(base,"Sort Value",SWITCH([Outcome_var],"Both SV and TPV exceed allowed %",1,"Within Limits",2,"SV Exceeds Allowed %, TPV Within %",3,10))
var minval = MINX(SortVal,[Sort Value])
return
SUMMARIZE(filter(SortVal,[Sort Value] = minval),[Outcome_var])
Hi Ankit,
Thanks so much for your response, I will try it out tomorrow.
I have a question though about the Summarize statement though, is there a way to only include selected values from a slicer? So when the user selects certain Case ID/ Customer Code combinations the results would be different in the summarised table?
