Volume Share - allselected concept

DAX DAX Calculations
1

Hello,

I can’t put pbix file here but I will try to explain it as much as possible.

Goal:

Filter by brand for any Volume Share Change vs Prior Period (Pts) >3, but have volume share and volume share change vs prior period (pts) values remain unchanged (same as before the filter was applied)

Problem:

When filtered, volume share and volume share change vs prior period (pts) recalculate based on the remining values

Notes:

Volume Share = volume sales of brand divided by total volume sales for that time period.

Data Timing: Data is refreshed monthly, but is weekly level data. Data is aggregated to show “Latest 4 Weeks” – a sum of the volume for 4 weeks

image
image894×281 20.4 KB

If I put a filter :slight_smile:

image
image1163×207 18.7 KB

Number changes. :frowning:

Goal:

Filter by brand for any Volume Share Change vs Prior Period (Pts) >3, but have volume share and volume share change vs prior period (pts) values remain unchanged (same as before the filter was applied)

Here are the formulas:

Volume Share Change vs Prior Period (Pts) =( ( 'Custom Measures'[Volume Share] ) - ( 'Custom Measures'[PP Volume Share] ) ) * 100

Volume Sales = 
CALCULATE(
	SUM(non_vmi_fact_data_final_history[volume_sales]),
	non_vmi_time_meta_data_final_history[Time Period] = "Latest 4 Weeks"
)


----

Volume Share =
VAR Share_of_Category =
    CALCULATE (
        SUM ( non_vmi_fact_data_final_history[volume_sales] ),
        non_vmi_time_meta_data_final_history[Time Period] = "Latest 4 Weeks",
        ( ALLSELECTED ( non_vmi_product_meta_data_final_history[custom_brand_value] ) )
    )
VAR Volume_Sales =
    CALCULATE (
        SUM ( non_vmi_fact_data_final_history[volume_sales] ),
        non_vmi_time_meta_data_final_history[Time Period] = "Latest 4 Weeks"
    )
RETURN
    ROUNDDOWN ( DIVIDE ( Volume_Sales, Share_of_Category ), 3 )


----

Time Period =
SWITCH (
    non_vmi_time_meta_data_final_history[Time Period Calc],
    0, "Latest 4 Weeks",
    -1, "Latest 4 Weeks",
    -2, "Latest 4 Weeks",
    -3, "Latest 4 Weeks",
    -4, "Prior Period",
    -5, "Prior Period",
    -6, "Prior Period",
    -7, "Prior Period",
    -52, "Year Ago",
    -53, "Year Ago",
    -54, "Year Ago",
    -55, "Year Ago"
)

-----


Time Period Calc =
non_vmi_time_meta_data_final_history[time_key]
    - MAX ( non_vmi_time_meta_data_final_history[time_key] )

Thanks,
-RK

2

Hello @rit372002, thank you for explaining your inquiry in detail :slight_smile:

However, I noticed you didn’t provide a PBIX file. May I confirm why are you not able to post it? Providing one will help users and experts find a solution to your inquiry faster and better.

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Yes, as I don’t have access to underlying data and the file is 500 MBs. :frowning:

So basically I am looking for this:

Even if I filter it with Period (Pts) > 3, I should get same value for JELLO which is 65.8% and 62.7% . The problem is total percentage changes because now only few products are selected.

I am not sure how to keep the % same even if only few items are selected or any filter is selected.

image
image792×245 21.8 KB

I hope it helps.

TIA
-RK

4

Hi,

If you are able to provide the pbix file i can try on that for now you can try removing the filter context of those column which are slicing it may help you .
You can give it a try.

Thanks,
Anurag

5

@Anurag Remove the filter context: Do you mean it by using REMOVEFILTERS?

Thanks,
-RK

6

@EnterpriseDNA
@Anurag

Here is the PBIX file:

https://drive.google.com/file/d/1cMN-HYg9z5Ij9VCyDS3BhdKirGKa4Upn/view?usp=sharing

To repharse the problem:

image

Market Share Tab: Currently % share change from last year for Jello is at 3%.

image
image1169×292 11.5 KB

“Want to retain the numbers” tab : If I select this in filter then I just get JELLO but market share zooms to 100 as no other product is selected. In the formula, we have ALLSELECTED.

image
image1170×105 5.55 KB

“Tried REMOVEFILTERS” tab: If I use to remove context, I only know to do it by using REMOVEFILTERS fucntion. When I did that, I get totally different number for Volumn share.

So I am confused.

Thanks for your help.
-RK

1 Like
7

Hi @rit372002 ,

You might want to look at this Power BI Accelerator Week #3. The bottom left hand corner of the report is keeping the based numbers. Review the videos that is within the link.

I hope this help.
Keith

3 Likes
8

Thank you for providing the PBIX file @rit372002.

Did the response provided by @Keith help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

9

@Keith Thanks Keith. This took me to the wild ride but ultimately I got it. Thanks for the suggestion. Really awesome initiative of accelerators.

3 Likes
10

@rit372002, you are very welcome. I’m glad i could help.
Thanks
Keith