Forum Discussion

DimD's avatar
DimD
Frequent Visitor
4 years ago

Measure with several calcul

Hello dear community,

I hope you are well.
I have a table of products, dirty, salesterritory.
I have a simple measure Total sales and another one of type : 

CPA base = if(and([Total sales]>=8000000,[Total sales]<= 70000000),"1","0")

 


I would like to create a matrix visualization with SalesterritoryKey, Region, Country as row and as value Total Sales and CPA base.
So far so simple, as you can see in the attached pbi.
But I would like a measure that makes a different calculation for the salesterritorykey but that would remain the same for the others


Example for the territory, I would like a formula that says:
For Salesterritory, the calculation is CPA+200 and for region and country it remains the value CPA

In this case I would like to see for SalesTerritoryKey 1 (1 CPA base 201, Northwest CPA Base 1 and UnitedStates CPA base 1; For SalesTerritoryKey 2 CPA base 200, Northeast CPA Base =0 and United States CPA base=0

Another measure 

For Region, the calculation is CPA+25 and for the salesterritory key and country CPA stay the same (example : Australia ( CPA base 26, sales territory key 9 CPA Base 1 andcountry Australia CPA base 1

 

Thank you in advance

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    DimD , Not very clear, but seem like you want to add up those

    sumx(values(Table[SalesTerritoryKey ]) , CPA base = if(and([Total sales]>=8000000,[Total sales]<= 70000000),"1","0"))

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • DimD's avatar
      DimD
      Frequent Visitor
      4 years ago

      hello amitchandak 

       

      I was sure I attached the file.

      I will try to be more clear:
      Here is what I have basic with the CPA base measure.

       

      You can see that I have 3 fields in rows
      Region
      SalesterritoryKey
      Country

      Example in the PBI
      Region Australia, then Salesterritorykey: 9 and finally country Australia.
      For these fields you see the values in CPAbase which are all 1.
      What I would like to do is for Region, find a measure that will add 200 to CPAbase but only for region and not for salesterritory and country which will stay at 1

       

      PBIX