Trying to set up calculation groups in order to show daily or monthly costs

[jaryszek]

Hello,

I have a model like that:

And trying to set up calculation groups to show amortized cost (daily or monthly) based on a selection from slicer. It should also adjust X-axis on the chart. So if Day is chosen, we want to have days on X-axis, if MonthName, we want to see Month total amortized cost. 

I put the slicer:

And now I want to show, based on selection,the total amortized cost daily or monthly:

Total Amortized Cost (Daily) = 
    SUM ( 'fct_amortizedcosts_daily'[CostInBillingCurrency] )

Total Amortized Cost (Monthly) = 
    SUM ( 'fct_amortized_monthly'[CostInBillingCurrency] )

My calculation group:

My chart:

How to do this? 
Is it possible to change X-axis based on selection? 

Maybe with using a field parameter connected to MonthName and Day? 
Can anybody help?

My model is on my disc:
https://drive.google.com/file/d/16EOv3bVoeVKhIpyb0AqhJnx3CVI_M3yy/view?usp=sharing

I have watched all available videos but didn't find the same schema approach like mine.

Best,
Jacek

[jaryszek]

So what i tried also:

1. I put Daily and Monthly Items like here:

2. I have put Date Hierarchy on X-axis and Total Amortized Cost (Measure is : 

Total Amortized Cost = 
0

3. Still is not switching automatically between daily and monthly. 

Here is my updated version for it:
https://drive.google.com/file/d/1Kwyuuk4cjd-qJhPdFZTkUsYZrNbGjJW0/view?usp=sharing

what is missing? 
Why It is not adjusting automatically when I am selecting slicer?

Best,
Jacek

[v-menakakota]

Hi @jaryszek ,

Try if bookmarks works in his scenario or else, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.

we truly appreciate your active participation in the Microsoft Fabric Community.

Best Regards, 
Community Support Team

[jaryszek]

Ok this is not helping. 

[v-menakakota]

Hi @jaryszek ,

Sorry for the inconvenience. Could you confirm if you have tried using bookmarks in this scenario?
Thank you.

[jaryszek]

No, I do not want to use bookmarks. I am pretty sure that calculated groups can work in this case but I do not know how to implement it. 

Another topic where a solution was provided for the daily/monthly switcher:
@MFelix even explained that calculation groups should be the same like wtihin the topic: 

https://community.fabric.microsoft.com/t5/Desktop/Switch-between-daily-and-monthly-fact-tables-with-...

But I do not know why they are not working. How theyare behaving. From my understanding theyar just replacing a measure before it is calculated...

Best,
Jacek

[MFelix · Accepted solution]

Hi @jaryszek  and @pbiuseruk 

Apologies for jumping in on this topic looking at the file you have provided you have a couple of questions when using the calculation group and the daily and montlhy.

• First you are not using the Field parameter on your x-axis to change the date
  • Calculation groups only change the calculations not the values of a axis
• Then you need to have a way to match the changes between both the field parameter and the x-axis
  • You can do that by sync slicers
  • However in this file you provide you have different names:

I have changed the name and them did this:

• Created two slicers
  • One for the Field Parameter
  • One for the Calculation Group

• Select one slicer and on the sync slicers select advance and wrute the group name:

• Use the field parameter on the x-axis

Now the slicers work has one and you can hide one of them

Concerning my answer on the other topic What I did is using two filed parameters related to each other, the relationship makes the selection of the other field parameter like a dimension table usually does for a fact table this will allow you to have different options without the need to generate more code specially if in the future you want to add more options, you just need to update the two field parameters.

[jaryszek]

Once again, you rule boss!!

Thank you very much

[jaryszek]

Anyone? 

Best,
Jacek

[v-menakakota]

Hi @jaryszek ,

I tested this scenario using a sample dataset on my side, and I was able to reproduce the behavior. Could you please review. I have replicated your scenario using field parameters and obtained the expected results. For your reference, I have attached the .pbix file. Please review it at your convenience.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 
Best Regards, 
Community Support Team

[jaryszek]

Thank you, 

It is not what I mean because you have added 2 measures per Day and Per Month. 
I want to show only one measure and use a calculation groups to switch between daily and monthly granularity depending on slicer value. 

Best,
Jacek

[v-menakakota]

Hi @jaryszek ,

I have updated the PBIX file to use one measure that switches between Daily and Monthly based on the selector.

Please check and let me know if the switching works correctly and if you see any issues.

Thanks

[jaryszek]

Thank you very much. 

Switcher is working, it is just usual parameter field. 

But in my topic I have different design than you... I have 2 granularity tables and costs coming from montly aggregated data and daily aggregated data. 
So my goal is to get different measures from them using calculated groups. 

If user will chose "Daily" I should use Total Amortized Cost based on Daily Costs, for Month based on "Monthly" table. 

Thank you for trying to do this. 

Best,
Jacek

[pbiuseruk]

The only way I've seen of changing the axis on a visual is to use field parameters - worth trying to inject the field parameter into the calculation group. I haven't got much experience with calculation groups but this is very easy to do with normal dax measures.

[v-menakakota]

Hi @jaryszek ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @pbiuseruk   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team  

[jaryszek]

Hi,

nope it didn't unfortunately it is just a general statement. 

[v-menakakota]

Hi @jaryszek ,

Could you please clarify the expected output and specify exactly what you need?

Thank you.

[jaryszek]

Hello,

I explained this in my first post, what is not clear for you?

Let me try to reiterate:

Summary of the goal
I want to build a dynamic Power BI report that shows Total Amortized Cost either by day or by month depending on the user’s slicer selection

What should happen
When the user selects Day the chart should show
The daily amortized cost measure
The Day values on the X-axis

When the user selects Month the chart should show
The monthly amortized cost measure
The Month names on the X-axis

Expected output
A single visual where both the measure and the X-axis level of detail day or month automatically switch based on the slicer selection, ideally achieved using a calculation group 

Best,
Jacek