Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
xyz9809
Frequent Visitor

Why is my query working on Desktop but not on service?

I currently use a single dataset for multiple powerBI reports that are based on service. So, I have to add/edit any measure in the desktop version of the main dataset report and then publish the dataset. This refreshes the data in all reports using the dataset.

 

I added this measure in the desktop version and it is working fine on the desktop but when I use it on the service, it doesn't work. Can someone help?

@amitchandak @lbendlin @Greg_Deckler @Ahmedx 

Please help

 

 

Amount Proportioned =
IF(SELECTEDVALUE(Acc[accountname]) in {"A","B","C"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"A","B","C"}),
 IF(SELECTEDVALUE(Acc[accountname]) in {"D","E","F"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"D","E","F"}),
    IF(SELECTEDVALUE(Acc[accountname]) in {"G","H","I"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"G","H","I"}),
        IF(SELECTEDVALUE(Acc[accountname]) in {"J","K","L"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"J","K","L"}),
Sum(loan[accname]])
))))

 

 

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

pls try this

Amount Proportioned =
VAR _accountname1 = SELECTEDVALUE(Acc[accountname]) in {"A","B","C"}
VAR _accountname2 = SELECTEDVALUE(Acc[accountname]) in {"D","E","F"}
VAR _accountname3 = SELECTEDVALUE(Acc[accountname]) in {"G","H","I"}
VAR _accountname4 = SELECTEDVALUE(Acc[accountname]) in {"J","K","L"}
VAR 
SWITCH(TRUE(),
 _accountname1, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]), CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"A","B","C"}))),
_accountname2,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"D","E","F"}))),
_accountname3, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"G","H","I"}))),
_accountname4,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"J","K","L"}))),
Sum(loan[accname]))

View solution in original post

8 REPLIES 8
Ahmedx
Super User
Super User

pls try this

Amount Proportioned =
VAR _accountname1 = SELECTEDVALUE(Acc[accountname]) in {"A","B","C"}
VAR _accountname2 = SELECTEDVALUE(Acc[accountname]) in {"D","E","F"}
VAR _accountname3 = SELECTEDVALUE(Acc[accountname]) in {"G","H","I"}
VAR _accountname4 = SELECTEDVALUE(Acc[accountname]) in {"J","K","L"}
VAR 
SWITCH(TRUE(),
 _accountname1, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]), CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"A","B","C"}))),
_accountname2,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"D","E","F"}))),
_accountname3, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"G","H","I"}))),
_accountname4,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"J","K","L"}))),
Sum(loan[accname]))
lbendlin
Super User
Super User

I recommend you add an account group column.  That will make the computation much simpler.

Can you elaborate?

For example:

lbendlin_0-1700000612287.png

 

Oh, i do have columns like these in my table. I am not sure why it is not working on online version 

aj1973
Community Champion
Community Champion

Hi @xyz9809 

What is the message you receive?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

I am not getting any error message. so for example in desktop version, this measure is giving me values after calculating the proportion of loan[accname]:

5
6
7
8

 

in web I am getting recalculated value of loan[accname]

aj1973
Community Champion
Community Champion

Weired,

I would recommend that you use variables in your measure and if you could replace IF by Switch.

It sounds weired also but doesn't hurt to try

Let me know 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.