Forum Discussion
Helper IIDynamic categories by sales
Hi,
We have a clasic start model, with Fact and Dims. Fact table is like below, with the IDs linked to DIM tables:
I want to create 3 categories of projects by sum of Sales and month: under 5(Projects with Sum of Sales<10), between 5 and 10, higher than 10. For eg in Jan 2022, we'll have Project1 and Project2 in the categorie 'between 5 and 10'(sum=6 and 5), Project3 in 'higher than 10'(sum=11) and display the results in a Stacked column chart:
I'm able to do it with Allexcept so far, but now the requiremnt changed into being able to aplply also slicers, like Service(ID_Service) and the projects to chnage dynamically the categorie considering also the slicers selected:
For eg in Jan 2022, if selecting ID_Service=B, to have Project1 in 'under 5'(sum=3) , Project2 and Project 3 in the categorie 'between 5 and 10'(sum=5 and 5).
Could you please offer some guidence?
Thank you!
Hi salexa ,
Please try:
Under 5 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))<5,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales]) Between 5 and 10 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))>=5&&CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))<=10,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales]) Higher than 10 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))>10,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales])Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-jianboli-msftCommunity Support
Hi salexa ,
Please try:
Under 5 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))<5,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales]) Between 5 and 10 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))>=5&&CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))<=10,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales]) Higher than 10 = var _a = ADDCOLUMNS('Table',"Flag",IF(CALCULATE(SUM('Table'[Sales]),FILTER('Table',[ID _ Proiect]=EARLIER('Table'[ID _ Proiect])))>10,1,0)) return SUMX(FILTER(_a,[Flag]=1),[Sales])Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
salexaHi,
The slicers are working with the categories.
Thanks for your help,
Irina