Sum per category and date

[Anonymous]

Hi, guys.

I have a list of PPI's along with their fact values and target values. The goal is when the PPI's are fitered on a visual (bar chart) it would not sum the target values as it does now. For example, if 2 PPI's are selected, it shows 2 bars for each for fact value and sums their target values. Is there a way to display target values separately for each PPI ? There are more thant 400 PPI's.Barchart

[Greg_Deckler]

@Anonymous - Can you post that sample data as text? Also, could you screen shot and share your configuration of that visual so I can see what type of visual it is as well as what columns/measures are where in the visual?

Is Target PPI a measure?

[Anonymous]

Sure, @Greg_Deckler ,

Sample data :

PPI name	Date	Target PPI values	Fact PPI values
PS/FN3/KL/11/PPR01	31 August 2020	1	0.75
PS/FN3/KL/9/PPR01	31 August 2020	1	0.57
PS/FN3/KL/8/PPR01	31 August 2020	1	0.97
PS/FN3/KL/11/PPR01	31 July 2020	1	0.75
PS/FN3/KL/9/PPR01	31 July 2020	1	0.43
PS/FN3/KL/8/PPR01	31 July 2020	1	1
PS/FN1/KL/11/PPR01	31 July 2020	0.85	0.9
PS/FN2/DL/5/PPR01	31 July 2020	0	0
PS/FN1/KL/8/PPR01	31 July 2020		183
PS/FN3/KL/7/PPR03	30 June 2020	1	1
PS/FN3/KL/7/PPR02	30 June 2020	0.99	0.98
PS/FN3/KL/7/PPR01	30 June 2020	0.6	0.5
PS/FN3/KL/11/PPR01	30 June 2020	1	0.3846
PS/FN3/KL/9/PPR01	30 June 2020	1	0.44
PS/FN3/KL/8/PPR01	30 June 2020	1	1
PS/FN1/KL/11/PPR01	30 June 2020	0.85	0.92
PS/FN4/KL/1/PPR01	30 June 2020	1	1
PS/FN2/DL/5/PPR01	30 June 2020	0	0
PS/FN1/KL/8/PPR01	30 June 2020		139
PS/FN3/KL/11/PPR01	31 May 2020	1	0.6
PS/FN3/KL/9/PPR01	31 May 2020	1	0.34

And it is a line and clustered column chart as per screenshot

Target and fact values both are measures. 

[Greg_Deckler]

@Anonymous - What is your Target measure formula?

[Anonymous]

@Greg_Deckler 

Target PPI = sum('Table'[Target value])

[Greg_Deckler]

@Anonymous - OK, you will need to change that measure in order to take into account whatever is in your legend. Although, should probably be doing that already unless you don't have a relationship somewhere. It's difficult to tell, can you share the PBIX or no?

[Anonymous]

No, not really.

In any case, I have a big flat table and also calendar table...

There's how I put my measures/ columns into the visual:

Axis: date

Legend: PPI name (PPI ID)

Value: Fact PPI value

Secondary values: Target PPI value

whenever customer is filtering PPI name via slicer I want target value to be shown per each PPI name (PPI ID). Should this be sumx?

[Greg_Deckler]

@Anonymous - OK, attached a PBIX of a mock-up, tell me what is not correct. Below sig, Page 7

Please @ me in replies otherwise will lose thread!

[Anonymous]

Thank you very much for your time and effort, @Greg_Deckler 

Attaching the example of what is wrong. There are 2 PPI's selected but target value is a sum. What customer is expecting to be able to select multiple PPI's and see target values for each and not summed.

[Greg_Deckler]

@Anonymous - OK, once I got my brain in gear, this can be solved for 2 PPI's pretty easily. More gets complex. See attached PBIX below sig, Page 7. 

Measure 7 = 
    VAR __PPI = MAX([PPI name])
RETURN
    SUMX(FILTER('Table (7)',[PPI name]=__PPI),[Target PPI values])


Measure 7a = 
    VAR __PPI = MIN([PPI name])
RETURN
    SUMX(FILTER('Table (7)',[PPI name]=__PPI),[Target PPI values])

[Greg_Deckler]

@Anonymous Did this work for you?

[Anonymous]

@Greg_Deckler , apologies for the delay, I thought  I posted the answer..

Unfortunatelly no, the solution does not serve the purpose as there are more than 400 PPIs and not sure which of those might be seletected at once. But appreciate your time and effort on this!

I think I will go with the completion precentage in this case. Might be a better solution 🙂

[Greg_Deckler · Accepted solution]

@Anonymous Sorry we couldn't figure out a way to do this. I have the model, I'll noodle on it over time and see if I can come up with something. Maybe there is a third-party visual that could do this better, or R/Python visual or Charticulator? Might be other avenues for you to explore.

https://community.powerbi.com/t5/R-Script-Showcase/bd-p/RVisuals

charticulator.com

[Greg_Deckler]

@Anonymous - OK, I don't know why this was such a struggle for me to wrap my brain around. The reason you have a single point is because the line is only affected by the X-Axis (date). The bar charts respect the Legend as well as the X-Axis. So, I don't see a way around this with the current visual. Will have to find another visual, let me look around for an answer.

[amitchandak]

@Anonymous , Take ppi as axis or legend.

Few steps you can find here

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart