Showing Values of Filtered Rows in Same Visual

DAX DAX Calculations
1

Hi,

I have a bar chart that displays Actuals which has visual level filter for set of current franchises.

I want to get the actuals of those filtered new franchises actuals and do a variance calculation.

So Basically to display actuals of current franchise & variances with new franchise however against same axis of current franchise.

Attached pbix

Please help !

Thanks,
Archer

Sample pbix.pbix (6.7 MB)

2

Hi Archer,

As far as I understand your question, you want normalized values per franchise, is this correct ?
Hopes below/attached PBIX answers your request.

Sample pbix DS.pbix (6.7 MB)

Added are 3 measures;

  1. Normalized =
    VAR CurrentCoverage = CALCULATE(SUMX(Data,Data[Actuals]),Data[Franchise] IN {“AWM”,“Recon”,“SPM”, “Trauma”, “ENT”})
    VAR OptimizedCoverage = CALCULATE(Sumx(Data,Data[Actuals]),Data[Franchise] IN {“AWM:ETS_95”,“ENT:ETS_95”,“Recon:ETS_95”,“SPM:ETS_95”,“Trauma:ETS_95”})

    VAR NormalizedFactor = CurrentCoverage / OptimizedCoverage
    Var NormalizedperFranchise = [Total Actual] * NormalizedFactor
    RETURN NormalizedperFranchise

  2. Total Actual = SUM( Data[Actuals] )

  3. VarianceNew = [Total Actual] - [Normalized]

To shorten the formulas in the measures, I have changed the table name from “vw_fct_tara_optimization_output” into “Data” and “Actual Values” into “Actuals” .

image
image1145Ă—584 37.2 KB

3

Hi @deltaselect

Thanks for responding with an action from your end.
I am actually looking to get the variance between Current and Optimized not looking for normalized factor.
A simple Optimized value minus Current Value.
For Example in the screenshot you posted in your response above
In the Optimized Table,
I am looking to get Current Actuals Values side by side like Recon:ETS_95 and Recon Value in same table and get a variance between them
I have attached excel file with what I am trying to achieve hope this will clarify confusion created by my requirement.

Thanks,
Archer

Optimized and Variance Plot.xlsx (15.3 KB)

4

Hi Archer,

Thanks for the explanation, sorry, I overlooked that.
Added a new version, give me your thoughts.

I added a table FranchiseInfo, whereby the franchise-types are grouped.
Measures are made for Total Current and Total Optimized, and Variance,

Please use “Franchise” from the FranchiseInfo table for your visuals (see the attached PBIX);
“Franchise” groups the 2 types of Franchises (Optimized and Current, as AWM ETS_95 and AWM ) per franchise together, so the variance will always appear correctly, regards if you select Optimized or Current in your visual, (it sums the 2 types) .
Also no filters needed in the visuals.

Sample pbix DS Variance.pbix (6.7 MB)

image
image1080Ă—634 30 KB

3 Likes
5

That’s it ! you nailed this one
Thank you so much @deltaselect

1 Like