Histograms to work with Group By Slicer

[Saloni_Gupta]

Hi All,

I have a Dimension table which has Client, Product and Job Id columns and I have a fact table which has my measures as (Revenue-Actual,Revenue-Budget,Revenue per hour-Actual, Revenue per hour-Budget, etc) at job level.

My requirement is to create histograms group by either Client or Product for Variance to Budget measures.
So, I need to create the bins and show the count of either client/product depending upon the user selection.

I have not shown the bins here because they can change according to the group by slicer or by different measures (Revenue, Revenue per hour, Cost per hour). Bins should see the min and max values of each measure and generate accordingly (not necessary to create using groups).

My approach would be to create a summarize table which would check user selection(on Group By) and aggregate the Variance measure accordingly, then based on this aggregated Variance - it should create the bins and count the number of Clients/Product falling in those bins.

Please let me know if you need more details.

Thanks in Advance!

[v-kaiyue-msft]

Hi @Saloni_Gupta ,

1. Create the calculation table and get the column names in the table.

Table 2 = {"Client", "Product"}

2. Create the field parameters.

3. Create measure.

Measure =
VAR _newvalue = SELECTEDVALUE('Table 2'[Value])
VAR _newcolumn =
SWITCH(
_newvalue.
"Client",SELECTEDVALUE('Table'[Client]),
"Product",MAX('Table'[Product])
)
RETURN CALCULATE(SUM('Table'[Variance-Revenue]),FILTER(ALLSELECTED('Table'),'Table'[Client]=_newcolumn||'Table'[Product]=_newcolumn ))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Saloni_Gupta]

Hi @v-kaiyue-msft ,
Thanks for replying on this post. 
I have exactly same tables (like Table2 and the one using Fields and parameter):

Now, to create histograms I am creating a measure which would generate x-axis (as mentioned in my question)

1) Histogram X-axis Value =

var _t = SUMMARIZE( 'DimBudgetToActuals' ,'DimBudgetToActuals'[Client] , "sumvariance" , [Variance to Budget - Revenue ($)])

var _max_value = MAXX(_t,[sumvariance])

var _min_value =  MINX(_t , [sumvariance])

return

GENERATESERIES(_min_value,_max_value,952055)

Can you suggest me how to use the above dynamic Client/Product selection in this measure as I am creating a Summarize table. Basically, If user selects Client in the slicer, it should summarize Variance Revenue by Client and then generateseries accordingly or else with the Product. Can you show me how its done using Summarize dax?