cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TomasJochec Frequent Visitor
Frequent Visitor

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.

 

pbi.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

sdfd.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you for your advises. 

 

Toma

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
TomasJochec Frequent Visitor
Frequent Visitor

Re: Slicer changing total

Hi,

 

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

*Product[C-WHTotalValueBuyPrice] ↓

total sum.JPG

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

 

graf.JPG 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Slicer changing total

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

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

Re: Slicer changing total

Hi @TomasJochec,

 

Could you please mark the proper answer as solution?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomasJochec Frequent Visitor
Frequent Visitor

Re: Slicer changing total

Hi,

 

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

*Product[C-WHTotalValueBuyPrice] ↓

total sum.JPG

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

 

graf.JPG 

Community Support Team
Community Support Team

Re: Slicer changing total

Hi @TomasJochec,

 

Thank you for sharing with us. The solution is very wonderful.

 

Best Regards,

Dale

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