Slicer selection - Cost allocation by department

[zubairs]

Hi Guys,

I am creating a model where I have to allocate admin cost between two departments. So I have deparmtent mapping which allows me to create a slicer and look at the cost of each department individually. I have created measures through which I can see the allocated cost i.e. Sales Cost = (Sales+(50% Admin))

Now I want the same functionality through slicers. So when I select let's say Sales & Admin, the cost being shown in the pivot is the total cost. Whereas, I want to see "Total Sales cost" & "50% Admin cost".Current totalCurrent SalesCurrent AdminWhat it should be

[v-yingjl · Accepted solution]

Hi @zubairs ,

You can create a measure like this to calculate:

Re = 
VAR _sum =
    CALCULATE ( SUM ( 'Table'[Values] ), 'Table'[Department] = "KSA Sales" )
        + 0.5
            * CALCULATE ( SUM ( 'Table'[Values] ), 'Table'[Department] = "KSA Branch Admin" )
RETURN
    IF (
        HASONEVALUE ( 'Table'[CE mapping] ),
        [Last Year],
        IF ( HASONEFILTER ( 'Table'[Department] ), [Last Year], _sum )
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zubairs]

Anybody please help. @AlexisOlson