Test Percentages.pbix (59.1 KB)
Test Data 2.xlsx (10.1 KB)
Hi there,
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.
Thanks
Sarah