ABC analysis in Power BI with DAX dynamically: Filtering by category separately question

Question

. I have a task about ABC analysis revenue by Clients, Items, SalesPersons, etc. And I've created it according to measures below. And now I'm strugling with filtering by categories A,B,C separately. For example, I made segmentation by A,B,C for Clients Revenue. And then I need to filter Items Revenue corresponding to "A" Clients Revenue. Table Revenue includes Client_key, Item_key, Amount fields. Maybe you can recommend a solution how to display now Item,Amount in a separate table which would correspond to Clients_key fitting for "A" category?

What I have:

1. The measure which I use to get Items and Revenue Amount is

_Key_Measures[NeededRevenue] = SUMX(filter(SUMMARIZE(Revenue,Revenue[Client_Key],Revenue[Item_Key],"Cat",_Key_Measures[ClientABC],"Rev",[TotalRev]),[Cat]="A"),[Rev])

But it doesn't work properly. As

SUMMARIZE(Revenue,Revenue[Client_Key],Revenue[Item_Key],"Cat",_Key_Measures[ClientABC],"Rev",[TotalRev])

isn't correctly summarized Client_key and Item_key (not all Item_key are summarized ).

But if we remove "Cat",_Key_Measures[ClientABC] from that, SUMMARIZation works properly.

2. _Key_Measure[ClientABC] = if([ClientRevC%] <=0.7, "A",if([ClientRevC%]<=0.9, "B","C"))

3. ClientRevC% = var CurrentTotal=SUM(Revenue[Amount]) var TotalRev=calculate([TotalRev], ALLSELECTED(Revenue)) var CumTotal= SUMX( filter(SUMMARIZE(ALLSELECTED(Revenue),Clients[Client],"Revenue Total",SUM(Revenue[Amount])), [Revenue Total]>=CurrentTotal), [Revenue Total]) return divide(CumTotal,TotalRev,0)

4. TotalRev = sum(Revenue[Amount])

Thank you in adavance.

Answer 1

I've solved the problem.

I've created Measure for calculating Revenue by Items according to the selected A,B,C category:

`TotalRevClientCategoryItem =
 var CatValue =SELECTEDVALUE(CategoryABC[CategoryABC])
 var tmpClients=  filter(calculatetable(SUMMARIZE(Revenue,Revenue[Client_Key],"Cat",_Key_Measures[ClientABC]),REMOVEFILTERS(Item[Item_Key])),[Cat]=CatValue)  --selecting clients accirding to A,B,C or All categories. REMOVEFILTERS(Item[Item_Key]) - it's used for selecting all Clients, not only those matching to the Item in the current row.
 var tmpClientswithLineAge = TREATAS(tmpClients,Revenue[Client_Key],CategoryABC[CategoryABC])  --restoring LineAge with Revenue table 
 return if(Catvalue="All",[TotalRev],CALCULATE(SUM(Revenue[Amount]),tmpClientswithLineAge))

`

Answer 2

Thank you for so fast and detail response. Yes, you are right, the steps suggested by you are more logical and correct. And in general it works, but not quite correctly again. And I've realised why? The reason is in dynamic filtering by A, B, C. At step 2,

CALCULATE([ClientABC], Revenue[Client_Key]=baseTable[Client_Key] -- the value you have in the row
                     ,Revenue[Item_Key]=baseTable[Item_Key] -- the value you have in the row
)

we get Clients segmenting by A, B, C categories based on Item_key in the row (A, B, C categories are formed dynamically according to measure [ClientABC]). That wouldn't be the list of all Clients segmenting by A, B, C.

And sure, I don't forget about report filters as well. I've coded it in the same way as you.

Thank you very much. I think I have to apply some ALL, ALLEXCEPT or Keepfilters in some place.
Or I can use the following measure where

1. I find all Clients fits to selected ABC category:

    tmpClients=    filter(SUMMARIZE(Revenue,Revenue[Client_Key],"Cat",_Key_Measures[ClientABC]), 
                   [Cat]="A")

2. Then I transformed these Clients to the list:

     c= CONCATENATEX (tmpClients,Revenue[Client_Key],", " ) 

and here I'm getting like: bf6a59c2-068c-11ec-b83a-0cc47aa8e53f, bf6a59c2-068c-11ec-b83a-0cc47aa8e53f, bf6a59c2-068c-11ec-b83a-0cc47aa8e53f

???but I need in quotes: "bf6a59c2-068c-11ec-b83a-0cc47aa8e53f", "bf6a59c2-068c-11ec-b83a-0cc47aa8e53f", "bf6a59c2-068c-11ec-b83a-0cc47aa8e53f", ...

3. Then I SUMMARIZE all Clients and Items and theirs Revenue:

    SUMMARIZE(Revenue,Revenue[Client_Key],                                  
              Revenue[Item_Key],"Revenue Total",SUM(Revenue[Amount]))

4. Filter only clients from 1 step:

FILTER(SUMMARIZE(Revenue,Revenue[Client_Key],                                           
       Revenue[Item_Key],"Revenue 
                    Total",SUM(Revenue[Amount])),Revenue[Client_Key] in {c})                                                 
           
                            

5. SUMX by filtering in step 4 and get Revenue.

THe result is:

    EVALUATE 
      var tmpClients=  
filter(SUMMARIZE(Revenue,Revenue[Client_Key],"Cat",_Key_Measures[ClientABC]),[Cat]="A")
      var c= CONCATENATEX (tmpClients,Revenue[Client_Key],", " ) 
                                    
      return { SUMX(FILTER(SUMMARIZE(Revenue,Revenue[Client_Key], 
                                     Revenue[Item_Key],"Revenue                                                         
                                      Total",SUM(Revenue[Amount]))
                           ,Revenue[Client_Key] in {c})
                    ,[Revenue Total])}

Answer 3

it's difficult to give a proper answer. First, I would ask about measures details (the code). Then, using of summirize and calculations in the same expression can cause problems, because of context change. So, I follow recomendations to devide the operation into 2 steps like:

1. Get the base without any calculation

--

  VAR baseTable = 
         SUMMARIZE(
                 Revenue
                 ,Revenue[Client_Key]
                 ,Revenue[Item_Key]
        )

2. Add measure results to the base. When you add measure results in ADDCOLUMNS you put you measure into a "row" context. In the row context the measure [ClientABC] will have the syntax like:

--

CALCULATE(
    [ClientABC]
    ,Revenue[Client_Key]=baseTable[Client_Key] -- the value you have in the row
    ,Revenue[Item_Key]=baseTable[Item_Key] -- the value you have in the row
)

So, with a following syntax, you get a controled value

VAR completeTable = 
        ADDCOLUMNS(
            baseTable 
            ,"Cat",_Key_Measures[ClientABC]
            ,"Rev",[TotalRev]
    )

Then the end of measure

VAR endTable = 
    FILTER(
            completeTable
            ,[Cat]="A"
    )

RESULT 
    SUMX(endTable,[Rev])

In my ABC I use following:

--.... some code then:

VAR Val = SELECTEDVALUE('_SetOf_3'[ABC - class],BLANK()) 
-- '_SetOf_3' is an auxiliary table with preseted values. I made a slicer of it.

VAR Real_filtered_byClass =
        FILTER(
            SalesBy_ABC
            ,[ABC]=Val
        )
    
VAR Result = 
    IF(
        ISBLANK(Val)
        ,SUMX(SalesBy_ABC,[Amount])
        ,SUMX(Real_filtered_byClass,[Amount])
    )

RETURN
    Result

Don't forget about your report filters as well. Hope this can help you. If not, then give your measures details.