Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TomasJochec
New Member

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

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 

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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.

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 

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.
v-jiascu-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.