Forum Discussion

TomasJochec's avatar
TomasJochec
New Member
8 years ago
Solved

Slicer changing total

Hello, 

 

I would like to ask about slicers. I have Total value of warehouse in selling prices, buying prices, etc. 

When I use slicer for category I get lower number of warehouse value = thats right = thats what I want.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

But I have also pie chart where I have at the begining all categories. Values are parts of TOTAL, as usual. But when I use slicer and I chose Category "A". Then the TOTAL is the same as total of Category. I understand, that filter = slicer removes all rows witch is not equal to my category, but I would like, somehow, to keep TOTAL of ALL categories and after using slicer I would like to see pie chart with TOTAL of ALL and one PART witch is covering by choosed category.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you for your advises. 

 

Toma

 

 

  • TomasJochec's avatar
    TomasJochec
    8 years ago

    Hi,

     

    so I found solution by myself. I had to make Column with TOTAL SUM in every single row. 

    *Product[C-WHTotalValueBuyPrice] ↓

    In other column I made SUM only for single product.

     

    Then in meassure I had to calculate witch part in percent is SUM from single product from TOTAL SUM.

    M-BuyPriceWarehouse= sum(Product[C-WHValueBuyPrice])/AVERAGE(Product[C-WHTotalValueBuyPrice])

    So When Product cost in Warehouse is 10 and TOTAL SUM of Warehouse stock cost is 100, I will get "0,1"  = 10 %

     

    Then I have to calculate opposit value = 90%, witch is second part in Pie chart.

    M-OppositeValue = (
         AVERAGE(Product[C-WHTotalValueBuyPrice])/AVERAGE(Product[C-WHTotalValueBuyPrice])-[M-BuyPriceWerahuse])
    )

    Then I can theese two meassures add to pie chart

     

     

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Toma,

     

    If I understand you scenario correctly, I'm afraid it's hard to achieve it. As we can see from my demo below, we can assign 0 to the unselected parts. But the 0 wouldn't hold its old portion. We can verify it from the definition of pie chart here.

    selectedValue =
VAR color =
    IF (
        HASONEVALUE ( ColorNames[ColorName] ),
        VALUES ( ColorNames[ColorName] ),
        BLANK ()
    )
RETURN
    IF (
        ISBLANK ( color ),
        SUM ( FactSales[SalesQuantity] ),
        IF (
            MIN ( DimProduct[ColorName] ) = color,
            SUM ( FactSales[SalesQuantity] ),
            0
        )
    )

    Slicer_changing_total

     

    Best Regards,

    Dale

    • TomasJochec's avatar
      TomasJochec
      New Member
      8 years ago

      Hi,

       

      so I found solution by myself. I had to make Column with TOTAL SUM in every single row. 

      *Product[C-WHTotalValueBuyPrice] ↓

      In other column I made SUM only for single product.

       

      Then in meassure I had to calculate witch part in percent is SUM from single product from TOTAL SUM.

      M-BuyPriceWarehouse= sum(Product[C-WHValueBuyPrice])/AVERAGE(Product[C-WHTotalValueBuyPrice])

      So When Product cost in Warehouse is 10 and TOTAL SUM of Warehouse stock cost is 100, I will get "0,1"  = 10 %

       

      Then I have to calculate opposit value = 90%, witch is second part in Pie chart.

      M-OppositeValue = (
           AVERAGE(Product[C-WHTotalValueBuyPrice])/AVERAGE(Product[C-WHTotalValueBuyPrice])-[M-BuyPriceWerahuse])
      )

      Then I can theese two meassures add to pie chart